As can be seen below an index device_fragment usage is log only when it should be data only, and a log device_fragment usage is data only when it should be log only. name db_size owner dbid created durability lobcomplvl inrowlen status -------- ------------- -------- ---- ------------ ---------- ---------- --------- ------------------------------------------------------------------------ a0000023 76000.0 MB a0000023 4 Sep 05, 2017 full 0 NULL select into/bulkcopy/pllsort, trunc log on chkpt, allow nulls by default device_fragments size usage created free kbytes ------------------------------ ------------- -------------------- ------------------------- ---------------- a0000023_data000 250.0 MB data only Sep 6 2017 9:37AM 86 a0000023_index000 500.0 MB data only Sep 6 2017 9:37AM 126 a0000023_log000 100.0 MB log only Sep 6 2017 9:37AM not applicable a0000023_data000 500.0 MB data only Sep 6 2017 9:37AM 56 a0000023_index000 500.0 MB data only Sep 6 2017 9:37AM 2554 a0000023_data000 300.0 MB data only Sep 6 2017 9:37AM 70 a0000023_index000 700.0 MB data only Sep 6 2017 9:37AM 422 a0000023_data000 1000.0 MB data only Sep 6 2017 9:37AM 126 a0000023_index000 1000.0 MB data only Sep 6 2017 9:37AM 0 a0000023_log000 548.5 MB log only Sep 6 2017 9:37AM not applicable a0000023_log000 0.5 MB data only Sep 6 2017 9:37AM 272 a0000023_log000 1.0 MB data only Sep 6 2017 9:37AM 0 a0000023_data000 2449.0 MB data only Sep 6 2017 9:37AM 112 a0000023_data000 2.0 MB data only Sep 6 2017 9:37AM 0 a0000023_index000 2422.5 MB data only Sep 6 2017 9:37AM 84 a0000023_index000 0.5 MB data only Sep 6 2017 9:37AM 0 a0000023_index000 3.0 MB data only Sep 6 2017 9:37AM 0 a0000023_data000 2997.0 MB data only Sep 6 2017 9:37AM 322 a0000023_data000 4.0 MB data only Sep 6 2017 9:37AM 0 a0000023_index000 1995.5 MB data only Sep 6 2017 9:37AM 658 a0000023_index000 0.5 MB data only Sep 6 2017 9:37AM 510 a0000023_index000 5.0 MB log only Sep 6 2017 9:37AM not applicable a0000023_log000 345.5 MB log only Sep 6 2017 9:37AM not applicable a0000023_log000 0.5 MB data only Sep 6 2017 9:37AM 0 a0000023_log000 4.0 MB data only Sep 6 2017 9:37AM 0 a0000023_index000 1495.5 MB data only Sep 6 2017 9:37AM 602 a0000023_index000 0.5 MB data only Sep 6 2017 9:37AM 0 a0000023_index000 5.0 MB data only Sep 6 2017 9:37AM 0 a0000023_data000 5495.0 MB data only Sep 6 2017 9:37AM 518 a0000023_data000 5.0 MB data only Sep 6 2017 9:37AM 0 a0000023_index000 1620.0 MB data only Sep 6 2017 9:37AM 28 a0000023_index000 5.0 MB data only Sep 6 2017 9:37AM 0 a0000023_data000 6995.0 MB data only Sep 6 2017 9:37AM 352250 a0000023_data000 3.0 MB data only Sep 6 2017 9:37AM 0 a0000023_index000 7747.0 MB data only Sep 6 2017 9:37AM 1638 a0000023_data000 6000.0 MB data only Sep 6 2017 9:37AM 140 a0000023_index000 2000.0 MB data only Sep 6 2017 9:37AM 56 a0000023_data000 4000.0 MB data only Sep 6 2017 9:37AM 98 a0000023_index000 15000.0 MB data only Sep 6 2017 9:37AM 6856024 a0000023_data000 10000.0 MB data only Sep 6 2017 9:37AM 10200000 -------------------------------------------------------------------------------------------------------------- log only free kbytes = 1018706

In 2 days, 1494 processes can not be terminated. This transactions, select data from IQ from ASE using by services. IQ was stopped yesterday and 1494 processes was occured. I killed kill spid but it was not terminated. what should i do to close, kill or stop these processes? I increase a parameter sp_configure 'max cis remote connections',1800 because service can not retrieve data from IQ to ASE... What should i do? Thanks CpuTime SPID DBName column4 HostName BlockingSPID SecondsWaiting WaitTime MemUsageKB SQLText RowsAffected StartTime ---------- ------- --------- ---------- --------------------- --------------- ----------------- ----------- ------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------- ----------------------- 356 3247 tempdb my2u weblogic2.localdomain (null) 91339 91519500 54 DYNAMIC_SQL dyn145: create proc dyn145 as select x, y from mytable where ID = 55728914 0 2018-09-24 09:53:10.843 56 3819 tempdb my2u weblogic2.localdomain (null) 91491 91671600 48 DYNAMIC_SQL dyn138: create proc dyn138 as select x, y from mytable where ID = 4572954 0 2018-09-24 09:50:39.043 55 4124 tempdb my2u weblogic1.localdomain (null) 90587 90767900 68 DYNAMIC_SQL dyn156: 0 2018-09-24 10:05:42.746 55 4169 tempdb my2u weblogic2.localdomain (null) 91570 91750000 70 DYNAMIC_SQL dyn246: 0 2018-09-24 09:49:20.646 54 4681 tempdb my2u weblogic3.localdomain (null) 90077 90257500 68 DYNAMIC_SQL dyn156: 0 2018-09-24 10:14:13.146 54 3621 tempdb my2u weblogic2.localdomain (null) 92437 92617400 70 DYNAMIC_SQL dyn162: 0 2018-09-24 09:34:53.243 54 5083 tempdb my2u weblogic3.localdomain (null) 89846 90026700 82 DYNAMIC_SQL dyn656: 0 2018-09-24 10:18:03.946 53 3158 tempdb my2u weblogic2.localdomain (null) 91798 91978800 48 DYNAMIC_SQL dyn138: create proc dyn138 as select x, y from mytable where ID = 63213 ... sp_who 3247 fid spid status loginame origname hostname blk_spid dbname tempdbname cmd block_xloid 0 3247 terminating my2u my2u weblogic2.localdomain 0 MYDB tempdb SELECT 0 I realized that my tran logs increasing from 1 Mb to 500 Mb... syslogshold result: dbid reserved spid page xactid masterxactid starttime name xloid ------- ----------- ------- -------- ------------ --------------- ----------------------- ------------------------------------------------------------------- -------- 7 0 0 1129 000000000000 000000000000 2013-03-24 17:36:13.36 $replication_truncation_point 0 9 0 3995 41965187 02805683000b 000000000000 2018-09-24 10:24:13.546 $chained_transaction 7990 9 0 0 41965187 000000000000 000000000000 2018-09-25 12:47:15.303 $replication_truncation_point 0 select object_name (id),* from master..syslocks where spid = 3995 GO id dbid page type spid class fid context row loid partitionid nodeid 259567215 9 0 4 3995 Non Cursor Lock 0 0 0 7990 0 (null) 546266866 9 0 4 3995 Non Cursor Lock 0 0 0 7990 0 (null) 451567899 9 0 4 3995 Non Cursor Lock 0 0 0 7990 0 (null) 579568355 9 0 4 3995 Non Cursor Lock 0 0 0 7990 0 (null) 1676634085 9 0 4 3995 Non Cursor Lock 0 0 0 7990 0 (null)

I am optimizing my SQL script and when i tried to run it i noticed the following; 1. Script full query execution (end to end) took more than 2hours to complete 2. Script query-by-query execution took 44 mins to complete I'm just wondering, why is my script full query execution took so much runtime than the same script query-by-query execution? Is there some hidden process happening inside the DB? Or any special case for those execution scenario? Tried to ask our DBA, but he seem to avoid the question maybe he doesn't know. I hope ill got some answers/leads here. Thanks

Where do we download SAP SAP SDK for ASE (Windows Server 2019)? Trying to connect to Sybase DB server using SQLDBX on Windows Server 2019 client and it cant find required dll (libsybct.dll). I I assume that I need to download SAP SDK for ASE but not sure where to download. Anyone know where to download?

After restoring a database from other environment is there a way to map a user in the database to a login in the server. I can drop and recreate or alias but in **MS SQL Server** there is a way: EXEC sp_change_users_login 'Auto_Fix', 'user' I was looking for something similar in **SYBASE ASE 16**

Is there a reason significant reason to recompile stored procedures? Of course the data in a database changes over time and the resultset of some stored procedures could differ due to changed underlying data. But unless there are changes of table structures, the stored procedures should be good forever? I'm talking about Sybase ASE 15.

I have my sort ordering in Sybase as: 45 190 **altdict** - Alternate (lower-case first) dictionary ordering Am I right that alternate means "aAbBcC..zZ" ordering? What should I do to change ordering with "ORDER BY" as 'ABC..YZabc..yz' ? What sort ordering to choose? Where may it be changed? In a session? In SQL statement? as far as I know I need to change the whole server sort ordering and rebuilt indexes. But there are a bunch of databases with different owners and different users and it seems a bit extreme

# Situation I am trying to troubleshoot performance when connecting to Sybase ASE Version 15.7 from Sqoop over JDBC. Though I cannot check things myself it appears to be 10x slower to extract data this way then with another tool (which uses ODBC). # Observation The DB team has observed the following: # Question: ### What is the meaning of FETCH_SIZE jconnect_implicit_1:? Of course other hints on what may be causing the performance issue are welcome as well. ---- The DB team believes it means a cursor is made for each row, perhaps because of this link which contains something vaguely similar , but I suspect it means something else as I have not found any reference of Oozie causing this over JDBC (we enforce a larger fetch size in the oozie command).

We have requirement to enable auditing on Sybase ASE(Adaptive Server Enterprise/15.7/EBF 27351 SMP SP139 /P/ia64/HP-UX B.11.31/ase157sp138x/4041/64-bit/FBO/Tue Aug 8 07:33:11 2017) and I enabled the same on master database as below: I was under impression that this would also enable auditing on any change in login/user(sp_addlogin, sp_modifylogin, sp_droplogin, sp_adduser, sp_dropuser) as create/drop/alter was handled at master level, but that was incorrect as this is applicable to only objects and not users/logins. Recently when I dropped one user, it was not logged in the audit event; after checking multiple documents on the Sybase documentation and different portals on the internet and also with the help of vendor, came to know that one more type of auditing(login_admin) needs to be enabled in order to capture these events in audit events. I executed below command and was expecting that this would solve the underlying issue: 1> sp_audit "login_admin", "all", "all", "on" 2> go Audit option has been changed and has taken effect immediately. (return status = 0) 1> sp_displayaudit "login" 2> go No logins currently have auditing enabled. (return status = 0) 1> As evident from the result of sp_displayaudit, logins are still not being audited. I am not really sure if this requires ASE reboot to take effect or I am missing something here. Any help would be greatly appreciated.

I need to retrieve 2 data records that were in a single table from yesterday's backup. - How can I restore a single table from a full backup in Sybase? - Or is it possible to retrieve the required records from the dump file itself? - Can I somehow load the dump file without recovering to the production database? I don't have enough disk space to recover the database to a temporary database on the same disks/server.

We are upgrading our hardware for Sybase ASE and minor upgrade in the Sybase ASE Service Pack Version. Upgrades are as follows: 1.Number of Cores - From 4 core to 8 core 2.Physical memory - 64 GB to 128GB. 3.ASE - Upgraded from "Adaptive Server Enterprise/15.7/EBF 21707 SMP SP110 /P/ia64/HP-UX B.11.31/ase157sp11x/3546/64-bit/FBO/Fri Nov 8 08:40:39 2013" to "Adaptive Server Enterprise/15.7/EBF 27351 SMP SP139 /P/ia64/HP-UX B.11.31/ase157sp138x/4041/64-bit/FBO/Tue Aug 8 07:33:11 2017" I have configured number of engines from 4 to 8 and max memory from 60GB to 100GB. can someone please advise what additional parameters should be changed in order to enhance performance of Sybase on the new server

I come from an Oracle background, and I'm used to the concept of OS users that can login to Oracle with DBA privileges in the machine the DB is installed on using their OS credentials as long as the OS user is part of a certain OS group (oinstall) using the below syntax: sqlplus / as sysdba In a machine (Oracle Linux 6.3) where Sybase ASE (15.7) is installed, is there any way I can login the same way as sa user?!

I'm working on migrating Sybase ASE 15.7 SP134 database to SQL Server 2008R2, using SSMA for Sybase 2014. I'm facing an issue where a table in SybaseDB has 2 columns with the data type TEXT. The SQL Server table with datatype "text" is mismatching on Japanese data characters. Questions are: 1. How can we keep same text as Sybase while migrating data? 2. How to keep Non-ASCII characters as it is during migration? Please comment if more information can be provided.

Spotted some answer excerpts on the question entitled "Why use both truncate and drop? ": > "TRUNCATE is logged, and it can be rolled back. ... Let me rebut these falsehoods. I am writing this rebuttal from a SQL Server perspective, but everything I say here should be equally applicable to Sybase." answered [Nov 8 '11 at 21:39](https://dba.stackexchange.com/a/7685/63644) Attempted to add comment but original post has been protected hence this new Question as a workaround. The above may be true for Microsoft SQL Server, it is NOT for Sybase - see ASE error 226 below 1> BEGIN TRAN 2> TRUNCATE TABLE xyz 3> go Msg 226, Level 16, State 1: Server 'ASE16', Line 2: TRUNCATE TABLE command not allowed within multi-statement transaction. 1> ROLLBACK 2> go Furthermore - I almost agree with the notion that truncate before drop should make no difference... 1. TRUNCATE TABLE on its own - involves some syslog activities (surprisingly) 2. DROP TABLE on its own (without proceeding truncate) + deallocate the table pages within an "empty" BEGIN/END Xacts pair + updates sysobject, sysindexes system tables & their indexes & such internally generated DMLs involve logging within BEGIN/END Xacts pair DROP TABLE on its own is marginally better as table page deallocation is not logged while truncate on its own seems to incur small amount of logging surprisingly. SYSLOG Audit Trail : truncate on its own (as shown below - TX seq is based on the most recent 5 transaction being displayed) Log Record Type Op TX seq ---------------------------------------- -- ----------- ======>Checkpoint Record 17 5 Begin Xact 0 5 Delete Extent Log Record 77 5 Direct Update/In Place Update 9 5 Update Record for DOL Table 65 5 End Xact 30 5 ======>Checkpoint Record 17 4 SYSLOG Audit Trail : drop table on its own (as shown below - TX seq is based on the most recent 5 transaction being displayed) Log Record Type Op TX seq ---------------------------------------- -- ----------- ======>Checkpoint Record 17 5 Begin Xact 0 5 Delete Extent Log Record 77 5 B-Tree Non-Leaf Delete 72 5 Delete Log Record for DOL Table 66 5 B-Tree Non-Leaf Delete 72 5 Delete Log Record for DOL Table 66 5 B-Tree Non-Leaf Delete 72 5 Delete Log Record for DOL Table 66 5 B-Tree Non-Leaf Delete 72 5 Delete Log Record for DOL Table 66 5 B-Tree Non-Leaf Delete 72 5 Delete Log Record for DOL Table 66 5 Deallocate Data Page 21 5 B-Tree Non-Leaf Delete 72 5 B-Tree Non-Leaf Delete 72 5 Delete Log Record for DOL Table 66 5 Deallocate Data Page 21 5 B-Tree Non-Leaf Delete 72 5 Delete Log Record for DOL Table 66 5 B-Tree Non-Leaf Delete 72 5 B-Tree Non-Leaf Delete 72 5 B-Tree Non-Leaf Delete 72 5 Delete Log Record for DOL Table 66 5 B-Tree Non-Leaf Delete 72 5 Delete Log Record for DOL Table 66 5 Update Record for DOL Table 65 5 B-Tree Non-Leaf Delete 72 5 Delete Log Record for DOL Table 66 5 B-Tree Non-Leaf Delete 72 5 Delete Log Record for DOL Table 66 5 End Xact 30 5 Begin Xact 0 4 End Xact 30 4 ======>Checkpoint Record 17 3

Once I connect to a database server without specifying a particular database, e.g. with: sqsh -Usomeuser -Ssomeserver ... I can do a: select db_name(); ... to see the default database for this user. But where is this information kept and / or how can I see the default databases configured for every user name or login name?

I am trying to move sybase user database from one drive to another drive, can you please help me with procedure. My database : Sybase ASE 16 OS windows.

I have this huge sql with a lot of left joins. Mostly on the tables primary key, so no problem. On one table though, my key could exist in either one column or the other. Syntax-wise that is no problem: LEFT JOIN table2 blk ON (t.rid=blk.transact OR t.rid=blk.transactchild) I have created two indices on table2, one for each of the keys, but my Sybase ASE 15.7 decides to table scan in stead. So I tried creating a single index with both of the keys and forcing it in the join, but that didn't help on performance. Showplan indicates, that the index is being used, but a scan takes forever. Is there a better way to join on either one or the other column?

I have 470 new database accounts in SYBASE ASE I certainly don't want to do it manually since it is definitely time consuming. Here by I have old script which I try to add logins Can someone help to me write this script in another way? Data present in this file will be like as below /tmp/addthese.txt: i111111 i222222 i333333 i444444 i555555 i666666 i777777 i888888 i999999 SCRIPT : source /cis1/sybase/SYBASE.sh set passs=cat /cis1/prod/dba/input/. ---- isql -Usa -SPDSQTS1 <