Skip to Content

Post upgrade behavior and new feature changes that impact the migration testing

Post upgrade behavior and new feature changes that impact the migration testing

Here is a link to the New Features Matrix, at a glance: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01165.1570/doc/html/jon1254257983781.html

Default is mixed, most need oltp: Adaptive Server 15.0 provides three optimization goals, ordered from “narrow” to “wide,” which correspond to the number of options and strategies that they allow the optimizer to consider:

allrows_oltp – is best for OLTP queries. allrows_oltp offers the narrowest selection of join methods: the query optimizer considers only nested-loop joins.

allrows_mix – is the default after upgrading to Adaptive Server 15.0. allrows_mix allows the optimizer to consider merge joins as well as parallel plans (if the Adaptive Server is configured for parallelism).

allrows_dss – is best for DSS queries. allrows_dss offers the widest selection of join methods. The optimizer considers hash joins, as well as nested-loop joins, merge joins, and parallel plans.

Procedure cache changes: Sybase recommends that you increase your procedure cache 2 – 6 times the size of your procedure cache in Adaptive Server 12.5.

If the procedure cache is filled with idle cached plans, and the CPU usage is not high, run the following dbcc commands instead. However, using these commands is likely to have a lesser effect than restarting the server.

dbcc traceon(757) go dbcc proc_cache(free_unused) go Do not use trace flag 757 in Adaptive Server versions earlier than 15.0.2 ESD #3.

You may need to increase the size of the data cache in Adaptive Server 15.0

Sybase recommends that you maintain up-to-date histograms for all columns referenced in where clauses, both when the where clauses are used as join predicates and as search arguments. Use the statistics advisor in QPTune to identify critical and missing statistics.

sp_opt_querystats output includes:

The query plan generated by showplan

Enabled trace flags and switches

I/O activity for the query generated by set statistics io

Missing statistics found for any of the tables involved in the query

The estimated plan cost calculated by the optimizer

The final plan and cost estimations calculated by the optimizer

The abstract plan for the query

The result of the query if the result set is executed (for example, if noexec is not on)

The logical operator tree for the query generated by set option show

Query execution time generated by set statistics time

After you execute the query, the query execution time generated by set statistics time

_______________________________________

ASE appears to be hung or hanging

The information that should be collected to report this

   * sybdiag -Usa -P -Stestserver * Collect a sybmon o > log on sammple of the behavior you will see: o > sample o > log close * 2-3 manual memdumps each 5 minutes apart 

Known CRs:

   A new feature, under CR 552424  was added to sybmon called 'hanginfo' to help us CR 696414  – Hang when issuing “online database” after using an unmount with encryption dump / load. Not to be fixed. Looks like the hang also can occur on create database. CR 687711  – Addresses hang and severe decrease in performance with Replication Agent. CR 687139  – ASE hang when OS host running Sysam is shutdown. 

Compression for Databases and tables

Setting this up is pretty simple but requires a special license: ASE_COMPRESSION

   * SySAM: WARNING: ASE functionality that requires the ASE_COMPRESSION license will be disabled on Sat 31 Mar 2012 01:06:30 PM MDT, unless a suitable ASE_COMPRESSION license is obtained before that date. 

The following parameters are needed to set this up:

   o enable compression = 1 or session level, set compression {on | off | default} o compression info pool size: Determines the size of the memory pool used for compression. Default is current server page size o capture compression statistics: used to enable the monTableCompression monitoring table to begin capturing compression statistics. 

Examples of size differences at table level:

    create table sales_comp (store_id int not null, order_num int not null, store_name char(20), store_desc varchar(500), current_date datetime not null) with compression = row go create index idx_sales_comp on sales_comp(store_id, order_num) go  1> sp_help sales_comp 2> go Name                 Owner      Object_type Object_status Create_date -------------------- ---------- ---------------------- -------------------------------------------------------------------------------------------- -------------------------------------- sales_comp           dbo        user table row level compressed, contains compressed data Jun  1 2012  8:27AM  (1 row affected) Column_name              Type             Length       Prec     Scale Nulls      Not_compressed               Default_name Rule_name          Access_Rule_name Computed_Column_object                       Identity ------------------------ ---------------- ------------ -------- ---------- ---------- ---------------------------- ------------------------ ------------------ -------------------------------- -------------------------------------------- -------------------- store_id                 int                   4       NULL      NULL 0                   0               NULL NULL               NULL NULL                                                  0 order_num                int                   4       NULL      NULL 0                   0               NULL NULL               NULL NULL                                                  0 store_name               char                 20       NULL      NULL 0                   0               NULL NULL               NULL NULL                                                  0 store_desc               varchar             500       NULL      NULL 0                   0               NULL NULL               NULL NULL                                                  0 current_date             datetime              8       NULL      NULL 0                   0               NULL NULL               NULL NULL                                                  0 Object has the following indexes  index_name                   index_keys index_description index_max_rows_per_page index_fillfactor index_reservepagegap index_created                          index_local ---------------------------- ---------------------------------------- ---------------------------------- ---------------------------------------------- -------------------------------- ---------------------------------------- -------------------------------------- ------------------------ idx_sales_comp                store_id, order_num nonclustered 0 0 0 Jun  1 2012  8:30AM                    Global Index  (1 row affected) index_ptn_name                                   index_ptn_seg ------------------------------------------------ -------------------------- idx_sales_comp_576002052                         default  (1 row affected) No defined keys for this object. name                 type                 partition_type partitions           partition_keys -------------------- -------------------- ---------------------------- -------------------- ---------------------------- sales_comp           base table           roundrobin 1           NULL  (1 row affected)  partition_name                           partition_id compression_level                    pages      row_count segment        create_date ---------------------------------------- ------------------------ ------------------------------------ ---------- ------------------ -------------- -------------------------------------- sales_comp_576002052                        576002052 inherit from table                       1              0 default        Jun  1 2012  8:27AM  Partition_Conditions ---------------------------------------- NULL  Avg_pages   Max_pages   Min_pages   Ratio(Max/Avg) Ratio(Min/Avg) ----------- ----------- ----------- --------------------------- --------------------------- 1           1           1                    1.000000 1.000000 Table LOB compression level 0 Lock scheme Allpages The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme. The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme.  exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts ------------ -------------- ---------- ----------------- ------------ ----------- 0              0          0                 0            0 0  (1 row affected) concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg ------------------------- --------------------- ------------------- 0                     0                   0 (return status = 0)  1> sp_spaceused sales_comp 2> go name                 rowtotal         reserved         data index_size           unused -------------------- ---------------- ---------------- -------- -------------------- ------------ sales_comp           0                32 KB            2 KB 2 KB                 28 KB  (1 row affected) (return status = 0)   create table sales_uncomp (store_id int not null, order_num int not null, store_name char(20), store_desc varchar(500), current_date datetime not null) go create index idx_sales_uncomp on sales_uncomp(store_id, order_num) go  1> sp_spaceused sales_uncomp 2> go name                     rowtotal         reserved         data index_size           unused ------------------------ ---------------- ---------------- -------- -------------------- ------------ sales_uncomp             0                32 KB            2 KB 2 KB                 28 KB 

No differences in sizes when built, add 50000 records and you start to see the differences in sizes.

   select start_time = convert( varchar(30), getdate(), 109 ) go insert into sales_uncomp values(1001,101, 'todds', 'low low prices', getdate()) go 50000 select end_time = convert( varchar(30), getdate(), 109 ) go select start_time = convert( varchar(30), getdate(), 109 ) go insert into sales_comp values(1001,101, 'todds', 'low low prices', getdate()) go 50000 select end_time = convert( varchar(30), getdate(), 109 ) go  1> sp_spaceused sales_uncomp 2> go name                     rowtotal         reserved         data index_size           unused ------------------------ ---------------- ---------------- -------------- -------------------- ------------ sales_uncomp             50000            4336 KB          2942 KB 1376 KB              18 KB   1> sp_spaceused sales_comp 2> go name                 rowtotal         reserved         data index_size           unused -------------------- ---------------- ---------------- -------------- -------------------- ------------ sales_comp           50000            3394 KB          2084 KB 1278 KB              32 KB  (1 row affected) (return status = 0) 

Varchar columns do not pre-allocate, so you will not see much compression. Since char columns are pre-allocated space, once the column is populated it can be compressed to remove the pre-allocations.

Examples of creating a database with compression:

   1> disk init 2> name = 'compdb_dat', 3> physname = '/home/rfisher/sybase/rel157/data/comp_db.dat', 4> size = '20M' 5> go 1> disk init 2> name = 'compdb_log', 3> physname = '/home/rfisher/sybase/rel157/data/comp_db_log.dat' 4> size = '10M' 5> go  1> create database compdb on compdb_dat = 20 2> log on compdb_log = 10 3> with compression = row 4> go CREATE DATABASE: allocating 5120 logical pages (20.0 megabytes) on disk 'compdb_dat' (5120 logical pages requested). CREATE DATABASE: allocating 2560 logical pages (10.0 megabytes) on disk 'compdb_log' (2560 logical pages requested). Database 'compdb' is now online. 

Many new trace flags have been added to help trace and diagnose compression and even change the behavior of it. Be very careful which of these you choose to use since some are not just for diagnostics, more at csope:

   15251 Print information about CPINFO structure. 15252 Print information while compressing or decompressing data row. 15255 Print information while compressing data page. 15259 Print the ERL information for compressed table. 15261 Trace the generation of character encoding. 15263 Force scan syscolumns rather than use CPINFO structure during dbcc page. 15265 Trace the usage of normal rows during page compression. 15266 Traceflag (similar to 1899) with inrow length = 4000 15267 Trace the expansion of column, row, page during compression. 15269 Ignore compression settings, if error during creating/altering table. 15271 Print information while compressing or decompressing INROWLOB column. 

Latest trace.h

More table and database compression at: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1570/html/commands/X75308.htm

Data cache default changes in ASE 15.7

A new default change is the tempdb_cache already bound to tempdb database. Also now using 60M instead of the old 8M:

    1> sp_cacheconfig 2> go Cache Name                           Status           Type         Config Value                 Run Value ------------------------------------ ---------------- --------------------------------------- ------------------------ default data cache                   Active           Default                   30.00 Mb                     30.00 Mb tempdb_cache                         Active           Mixed                     30.00 Mb                     30.00 Mb 

Database options that have been added to sp_dboption

Fully recoverable DDL – This new feature allows you to fully recover using a dump transaction of your database, these normally minimally logged transactions will become fully logged.

   full logging for all full logging for alter table full logging for reorg rebuild full logging for select into 

>>>>How does this impact performance? Test this

allow wide dol rows - Expanded Variable-Length Rows, Adaptive Server version 15.7 redefines data-only locked (DOL) columns to use a row offset of up to 32767 bytes.

                      A logical page size of 16K is required in order to create wide, variable-length DOL rows. This is disabled by default, once you enabled this new feature the database can no longer be downgraded. 

async log service - Asynchronous log service, or ALS, enables great scalability in Adaptive Server, providing higher throughput in logging subsystems for high-end symmetric

                    multiprocessor systems. However, to use this feature, your ASE must have 4 engines or more. 

Automatically creating identity columns in tables. When the Adaptive Server auto identity database option is enabled, an IDENTITY column is added to any tables that are created in the database. The column name is CIS_IDENTITY_COL, for proxy tables, or SYB_IDENTITY_COL, for local tables. In either case, the column can be referenced using the syb_identity keyword. These are all off by default and will not impact a system unless enabled manually:

   auto identity option identity in nonunique index unique auto_identity index 

Downgrade steps from ASE 15.7

   * Using any of the new features in ASE 15.7 can prevent you from downgrading below 15.0.2 ESD #3. * Returning to 15.0.2 ESD #3 is easier than downgrading to other versions, because this version can handle a number of error conditions that may arise as a result of downgrading. If you return to a pre-15.0.2 ESD #3 version of Adaptive Server, no error handling is provided. * Using the new features can result in inexplicable behavior, including misleading error messages and stack traces, unless you are uninstall all usages of the new features reported by sp_downgrade. * Detailed down grade instructions can be found per OS: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc35892.1570/doc/html/ate1314480169293.html 

Drivers required for ASE 15.7 Compatibility

The Software Developers Kit for ASE client versions included with versions of the ASE.

   * SDK 12.5.1 is in ASE 12.5.1 through ASE 12.5.4 * SDK 15.0 is in ASE 15.0 through ASE 15.03 * SDK 15.5 is in ASE 15.5 * SDK 15.7 is in ASE 15.7 

SDK 15.7 connectivity tools will connect to older version of the ASE starting from 12.5.4 and higher.

There is an interoperability matrix set for SDK 15.7 for each platform this one is for Windows: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc74865.1570/html/sdkrbwin/BHABFJAD.htm

Migrating OCS application from 12.5x to 15.7 which includes ct2lib, ctlibs, db2ct, dblibrary, esqlc, esqlcob

   * Applications need to be recompiled with the new libraries. 

To build applications on unix

   * Notice the sybopts.sh file there is one for each type of binary used ct2lib, ctlibs, db2ct, dblibrary, esqlc, esqlcob in the OCS-15_0/samples/ * Any and all new switches with the various compiler versions are listed that are required sybase libraries to work properly for the compiler. * You can also look in the Open Client and OpenServer Programmers Supplement for UNIX or Windows and under each type of programming you see a Compile-and-link-lines for building threaded and non-threaded applications for each platfrom. * Here is link for the ctlibs in 15.7:http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc35456.1570/html/ocspsunx/ocspsunx8.htm * Please note the certification of the version of the SDK you are using and the compiler version for the various programs and keep with what we are certified with ref:http://certification.sybase.com/ucr/search.do  Changes: * From 12.5x to 15.x we added syb to the libraries so libct became libsybct. * To build 64 bit applications you need to add the -DSYB_LP64 to your build. * To build thread-safe libraries you must compile with -mt -D_REENTRANT with cc. * If using any security services in your older applications please note that the $sybase/OCS-12_5/lib3p/csi folder got moved so the files are directly in the $sybase/OCS-15_0/lib3p folder (the csi folder was in SDK 12.5.1 ESD#12 though SDK 15.0 ESD#22) 

Migrating ODBC driver for ASE

   * On Windows starting with SDK 12.5.1 ESD #12, Sybase created it's own native odbc driver. The driver changed from Sybase ASE ODBC driver to Adaptive Server Enterprise. * For the new odbc driver to work correctly you need to make sure your ASE has the most current odbc MDA scripts for the user with the most current version 

of odbc. You check this on the ASE by doing an sp_version. The odbc MDA scripts are in the $sybase/DataAccessxx/ODBC/sp folder on the client install of the SDK. To run these you go to that directory and type install_odbc_sprocsYourASEnameintheinterfacesfile YourASElogin YourASEPassword. This makes sure the older ASE version can connect to a newer version of the driver. This adds any new features like datatypes to the odbc MDA scripts. This also has fixes in it. You can read this information in the EBF#####_README.txt file that comes with the SDK EBF isntallation.

   * The changes from the old driver to the new are listed in this whitepaper: ASE ODBC Driver and OLE DB Provider Migration Technical Whitepaper: http://www.sybase.com/files/White_Papers/ASE-ODBC-OLE-DB-Migration-032409-WP.pdf  Changes: * Name change from using OEM driver to Sybase/SAP native odbc driver. * Starting with SDK 15.0 ESD #4 for Windows 64 bit or higher we offered a 64 bit odbc driver. * Starting with SDK 15.5 ESD #5 or higher for various 64 bit unix platforms we now have a 64 bit odbc driver. * Only Windows and linux have both a 32 and 64 bit odbc driver. 

Migrating OLE DB driver for ASE

   * On Windows starting with SDK 12.5.1 ESD #12, Sybase created it's own native OLEDB driver. * The driver changed from Sybase ASEOLEDBProvider and Sybase ASE OLE DB Provider to ASEOLEDB or Sybase OLEDB Provider. * For the new ole db driver to work correctly you need to make sure your ASE has the most current oledb MDA scripts for the user with the most current version of ole db. You check this on the ASE by doing an sp_version. The oledb MDA scripts are in the $sybase/DataAccessxx/OLEDB/sp folder of the client SDK install. To run these you go to that directory and type install_oledb_sprocs YourASEnameintheinterfacesfile YourASElogin YourASEPassword. This makes sure the older ASE version can connect to a newer version of the driver and adds any new features like datatypes to the oledb MDA scripts. This also has fixes in it. You can read this information in the EBF#####_README.txt file that comes with the SDK EBF isntallation. * The changes from the old driver to the new are listed in this whitepaper: ASE ODBC Driver and OLE DB Provider Migration Technical Whitepaper: http://www.sybase.com/files/White_Papers/ASE-ODBC-OLE-DB-Migration-032409-WP.pdf  Changes: * Name change of the driver from using the OEM driver to Sybase/SAP native oledb driver. * Starting with SDK 15.0 ESD#8 for Windows 64 bit or higher we offered a 64 bit oledb driver. 

Migrating ADONET for ASE

   * SDK 15.7 no longer supports the 1.1x version of .net. o The Sybase.Data.AseClient.dll is no longer included, so you will have to migrate these older applications to use the .net 2 or higher and migrate the Sybase adonet driver to Sybase.AdoNet2.AseClient.dll or higher. * For the adonet driver to work correctly you need to make sure your ASE has the most current oledb MDA scripts for the user with the most current version of adonet. The oledb and adonet driver use the same set of oledb MDA tables on the ASE. You check this on the ASE by doing an sp_version. The adonet MDA scripts are located in the $sybase/DataAccessxx/ADONET/sp folder of the SDK install on the client. To run these you go to that directory and type install_adonet_sprocs YourASEnameintheinterfacesfile YourASElogin YourASEPassword. This makes sure the older ASE version can connect to a newer version of the driver and adds any new features like datatypes to the odbc MDA scripts. New fixes are also included. You can read this information in the EBF#####_README.txt file that comes with the SDK EBF isntallation. * Starting with SDK 15.7 ESD#2 we included all the unmanaged files and both the 32 and 64 version in one file. o When you build a .net application you only need to include and reference one file either the Sybase.AdoNet2.AseClient.dll or the Sybase.AdoNet4.AseClient.dll. o You no longer need to include the sbgse2, sybdrvado20.dll for 15.0 versions. o You no longer need to include the sbgse2, sybdrvado20.dll, sybcsi_certicom_ftps26.dll, sybcsi_cor26.dll, sybcsi_profiler26.dll from the SDK 15.5 base through SDK 15.7 ESD#1.  Changes: * Starting with SDK 15.0 ESD #10 or higher offered a adonet driver Sybase.AdoNet2.AseClient.dll to be used with .net 2.0 through 3.5. * Starting with SDK 15.5 ESD #9 or higher offered a adonet driver Sybase.AdoNet4.AseClient.dll to be used with .net 4.0 or higher. * Starting with SDK 15.7 ESD #2 or higher only need one .dll included and referenced in project to build 32 or 64 bit project. * Dropped support of the .net 1.1x and the Sybase.Data.AseClient.dll is no longer included in the SDK 15.7. 

New features included of SDK 15.7 and notes about the SDK

   * Adaptive Server Enterprise Extestion Module for Python * Adaptive Server Enterprise Extension Module for PHP * Adaptive Server Enterprise Database Driver for PERL * Support for LOB * Starting with SDK 15.5 and higher all the EBFs are complete install packages. You NO longer need a base install. * All fixes only go in current EBF/ESD of the product. If you find a bug in SDK 15.5 ESD#5 it would be fixed in SDK 15.7 ESD #current. * EBFs are cumulative of all the fixes in the previous version. * SDK 15.7 is supported on Windows 7, Windows 2008 and Windows 2008 R2 64 bit releases. 

Enable functionality grouping

Use enable functionality group to enable or disable all configuration parameters in this group. Enable or disable individual configuration parameters to overwrite the group value.

   enable functionality group - Enables or disables these features in Adaptive Server versions 15.7 and later: o Shareable inline defaults o Select for update o Like pattern matching o Quoted identifiers o Unicode noncharacters o Monitor cursor statements o Reduce query processing latency 

Configuration parameters

Adaptive Server 15.7 introduces other new configuration parameters as well.

   * automatic master key access:  Determines Adaptive Server operates in unattended startup mode * capture compression statistics:  Enables the monTableCompression monitoring table to begin capturing compression statistics * column default cache size:  Determines the size of the cache that Adaptive Server must keep in memory to provide defaults for nonmaterialized columns * compression info pool size:  Determines the size of the memory pool used for compression * disable varbinary truncation: Controls whether Adaptive Server includes trailing zeros at the end of varbinary or binary null data * enable console logging :  Once enabled, Adaptive Server sends messages to the console separately from the error log after startup 

Encountering stack trace(s)

   * sybdiag -Usa -P -Stestserver * Research on the uncommon modules of the stack trace for existing bugs * Review CSOPE for the modules being reported with what you are seeing in the log (SQL outputs if any) * Configure for a shared memory dump based on stack type. NOTE:CR 697612  – “ASE running in threaded mode does not generate shared memory dump if CSMD condition is encountered by a non-engine thread.” 

Scheduled CRs to be fixed in ASE 15.7 ESD#2.

   CR 689860  – “ASE will fail to dump shared memory when ‘number of dump threads’ is configured to 2 or more.” Fixed in ASE 15.7 ESD#1 

Known upgrade CRs:

   CR 646353  - signal 11 raised when upgrade step 245 is involved during upgrade to ASE 15.5 smp. This affects 15.7 as well, fixed in #1. Look for infected with 11 in the following modules upgd__init_sysroles sprintf strlen. CR 669429  Infected with 11 in function testpasswd following an upgrade. Work around reset password with sp_password 

Post-Upgrade CRs:

   CR 705999  / CR 700640  (OCS bug) – “Killing CIS spid with remote i/o causes segmentation violation and crashes dataserver” Targeted for fix in ASE 15.7 ESD#2 / SDK 15.7 ESD#4 CR 695135  – ‘ASE reports the message in the errorlog: " Expected object passed to kmuxtskGetThreadpool is not of type Multiplexed Task"’ Targeted for fix in ASE 15.7 ESD#2 CR 697533  - Solaris specific, results in sddone and/or 694, 823 errors 

Errors in the ASE errorlog

   * Collect ASE log with last server recycle (errorlog) * The server configuration file (servername.cfg) * sybdiag -Usa -P -Stestserver * Along with any errors being reported to the process being ran or applications involved 

Known CRs:

   CR 688735  Alter-table-modify-column commands using fully-qualified table names, where the table-name portion requires quoted identifiers may fail with a syntax error. Not fixed, target #2. CR 697533  ASE on Solaris platforms reports 694 or 823 errors along with messages like "kernel sddone: write error on virtual disk 3 block 145536", "kernel sddone: 16384 bytes passed, 0 returned on write for virt disk 3 block 145536" or "kernel  sddone: read error on virtual disk 6 block 6812351", "kernel  sddone: I/O error". There are no corresponding disk i/o error messages reported in the Operating System logs. Fix targeted for ASE 15.7 ESD#2 

HP setting introduced in ASE 15.7

Enables asynchronous I/O on HP-UX 11.31 and later

   * Setting  enable hp posix async i/o  to 1 enables asynchronous I/O on database devices created on on HP-UX 11.31 and later file systems. You must first enable Adaptive Server to use asynchronous I/O with the  allow sql server async i/oconfiguration parameter. * “enable hp posix async i/o”  improves performance when you allocate database devices on file systems, but may decrease performance on database devices that are allocated on raw devices.Trace flag for this: * Trace flag 1666 Enables the use of the HP Posix AIO 

Asynchronous I/O and Direct I/O is now enabled for the backup server for VxFs file system

   * A new traceflag was introduced -D1024 * When the traceflag is turned on, it performs HP Posix Async IO for VxFs file system and Raw device. 

Huge pages on Linux formula

The CPU-Cache translation look aside buffer (TLB) stores information about conversions from an virtual page address to the physical page address, and every byte access to physical memory requires a conversion (called a “cache miss”). Although these cache misses are very expensive, you can improve the TLB hits is by enabling “huge pages” on Linux machines.

How HUGE PAGES look in the ASE errorlog by default:

   00:0000:00000:00000:2012/03/19 13:30:18.59 kernel Could not allocate memory using Huge Pages. Allocated using regular pages. For better performance, reboot the server after configuring enough Huge Pages. 

NOTE: The documentation has a bug about this reported under CR 649938 .

Adaptive Server adjusts its shared memory up to the nearest multiple of 256MB.

For example, if you configure Adaptive Server with 800MB of shared memory, it is rounded off to 1GB (some versions of Linux do not allow you to allocate huge pages if the size is not a multiple of Hugepagesize).

Before starting Adaptive Server, check /proc/meminfo to make sure Linux already has huge pages configured:

    cat /proc/meminfo .... HugePages_Total:       0 HugePages_Free:        0 HugePages_Rsvd:        0 HugePages_Surp:        0 Hugepagesize:       2048 kB 

Memory you allocate for huge pages is used for the shared memory only, shmall. If you allocate too many huge pages, this may lead to Adaptive Server excessively swapping physical pages. You should only allocate the required number of huge pages. The <number of hugepages> should be such that the amount of memory allocated for huge pages usage should be close to (ideally match) the amount of memory allocated for system V shared memory (e.g. shmall).

   Example, if 42 GB is allocated to system V shared memory then this applies cat /etc/sysctl.conf # getconf PAGESIZE 4096 # sysctl -a | grep shmal kernel.shmall = 21000000 # sysctl -a | grep hugep vm.nr_hugepages = 20510 

NOTE: that PAGESIZE is the size of the normal/regular pages (not hugepages). The shmall value is in KB. These lines will need to be added to /etc/security/limits.conf for the user that starts ASE.

   In this sample below the user is Sybase: sybase soft memlock unlimited sybase hard memlock unlimited 

After this when you start ASE a line in the errorlog should show that allocation of memory as hugepages succeed.

Adaptive Server version 15.0.3 and later allocates shared memory using huge pages by default. However, if the system does not have enough huge pages—or is not configured for huge pages,—Adaptive Server uses regular pages, and writes this message to the error log. After you have set your OS and ASE to the formula guidelines above you are ready to enable huge pages, time to start Adaptive Server with traceflag 1653.

Kernel Mode options of Process Mode versus Threaded Mode (default)

The kernel for which you configure Adaptive Server determines the mode in which Adaptive Server runs:

   * Threaded mode – Adaptive Server runs as a single multi threaded operating system process and processes SQL queries with engines running on threads in thread pools. Threaded mode utilizes threads without engines to manage I/O. Administrators can configure additional thread pools to manage work load. * Process mode – The legacy kernel on which Adaptive Server previously ran. In process mode, Adaptive Server runs as multiple operating system processes that cooperate to work as a single server. Process mode uses engines to manage I/O, and administrators configure engine groups to manage work load. 

Why is the new threaded mode the default in ASE 15.7?

   * Here’s a link to David Wein’s Techwave 2011 presentation on threaded mode that discusses the benefits of this model: http://www.sybase.com/techwave/TWpresentationarchive Once on the site, got to TechWave 2011 Presentations -> Data Management Presentations -> DMASE221 "Introducing ASE 15.7's New Threaded Kernel" 

If you are NOT running on the newer hardware with multi-threading (SMT) at the OS level enabled, you will do best to run with “process mode” instead of “threaded mode”. Until you get onto new hardware that supports threading of some sort. To change to “process mode”, a recycle is required:

     o sp_configure 'kernel mode',0,'process' 

NOTE: Process mode is much like compatibility mode, this should not be looked at as a permanent solution.

To determine the size, in 2K pages, of the kernel resource memory pool from which all thread pools and other kernel resources are allocated memory:

     o sp_configure  ‘kernel resource memory’ 

If you are using threaded kernel, how does it show up in the errorlog?

     00:0000:00000:00000:2012/03/01 12:06:33.61 kernel  Using value of 'number of engines at startup' (1) for size of syb_default_pool. 00:0000:00000:00000:2012/03/01 12:06:33.61 kernel  Create Thread Pool 3, "syb_blocking_pool", type="Run To Completion", with 4 threads 00:0000:00000:00000:2012/03/01 12:06:33.61 kernel  Create Thread Pool 2, "syb_system_pool", type="Run To Completion", with 1 threads 00:0000:00000:00000:2012/03/01 12:06:33.61 kernel  Create Thread Pool 1, "syb_default_pool", type="Engine (Multiplexed)", with 1 threads 00:0007:00000:00000:2012/03/01 12:06:33.63 kernel  I/O controller 2 (NetController) is running as task 1123125208 on thread 7 (LWP 5310). 

For process mode you will see pretty much a normal ASE errorlog:

     00:0000:00000:00000:2012/03/19 14:22:38.67 kernel  engine 0, os pid 17060  online 

New Sysmon output for Threaded Mode

     Kernel Utilization ------------------ Engine Utilization (Tick %)   User Busy   System Busy    I/O Busy        Idle -------------------------  ------------  ------------  ----------  ---------- ThreadPool : syb_default_pool Engine 0                         0.0 %         3.5 %       0.2 %      96.3 % Average Runnable Tasks            1 min         5 min      15 min  % of total -------------------------  ------------  ------------  ----------  ---------- ThreadPool : syb_default_pool Global Queue                       0.0           0.0         0.0       0.0 % Engine 0                           0.7           0.9         0.8     100.0 % -------------------------  ------------  ------------  ---------- Server Summary      Total           0.7           0.9         0.8 Average           0.3           0.5         0.4  CPU Yields by Engine            per sec      per xact       count  % of total -------------------------  ------------  ------------  ----------  ---------- ThreadPool : syb_default_pool Engine 0 Full Sleeps                    51.9         222.6        3116      99.9 % Interrupted Sleeps              0.1           0.3           4       0.1 %  Thread Utilization (OS %)     User Busy   System Busy        Idle -------------------------  ------------  ------------  ---------- ThreadPool : syb_blocking_pool : no activity during sample ThreadPool : syb_default_pool Thread 2    (Engine 0)           0.1 %         0.1 %      99.8 % ThreadPool : syb_system_pool : no activity during sample -------------------------  ------------  ------------  ---------- Server Summary    Total           0.1 %         0.1 %     899.8 % Average           0.0 %         0.0 %     100.0 % Adaptive Server threads are consuming 0.0 CPU units. Throughput (committed xacts per CPU unit) : 100.0 Page Faults at OS               per sec      per xact       count  % of total -------------------------  ------------  ------------  ----------  ---------- Minor Faults                       0.3           1.3          18     100.0 % Major Faults                       0.0           0.0           0       0.0 % -------------------------  ------------  ------------  ----------  ---------- Total Page Faults                  0.3           1.3          18     100.0 % Context Switches at OS          per sec      per xact       count  % of total -------------------------  ------------  ------------  ----------  ---------- ThreadPool : syb_blocking_pool Voluntary                          0.0           0.0           0       0.0 % Non-Voluntary                      0.0           0.0           0       0.0 % ThreadPool : syb_default_pool Voluntary                         56.1         240.5        3367      73.4 % Non-Voluntary                      4.3          18.5         259       5.6 % ThreadPool : syb_system_pool Voluntary                         16.1          68.9         964      21.0 % Non-Voluntary                      0.0           0.0           0       0.0 % -------------------------  ------------  ------------  ----------  ---------- Total Context Switches            76.5         327.9        4590     100.0 %   CtlibController Activity        per sec      per xact       count  % of total -------------------------  ------------  ------------  ----------  ---------- Polls                             0.0           0.0           0       0.0 % DiskController Activity         per sec      per xact       count  % of total -------------------------  ------------  ------------  ----------  ---------- Polls                            414.2        1775.3       24854       n/a Polls Returning Events             4.1          17.7         248       1.0 % Polls Returning Max Events         0.0           0.0           0       0.0 % Total Events                       4.1          17.7         248       n/a Events Per Poll                    n/a           n/a       0.010       n/a NetController Activity          per sec      per xact       count  % of total -------------------------  ------------  ------------  ----------  ---------- Polls                              1.0           4.3          60       n/a Polls Returning Events             0.0           0.0           0       0.0 % Blocking Call Activity          per sec      per xact       count  % of total -------------------------  ------------  ------------  ----------  ---------- Total Requests                      0.0           0.0           0       n/a =============================================================================== 

A new system stored procedure has been introduced to help us see these settings as well:

     1> sp_helpthread 2> go Name                               Type CurrentSize            TargetSize           IdleTimeout Description InstanceName ---------------------------------- ---------------------------------------- ---------------------- -------------------- ---------------------- ---------------------------------------------------------------------------------------- ------------------------ syb_blocking_pool                  Run To Completion 4                     0                     0 A pool dedicated to executing blocking calls NULL syb_default_pool                   Engine (Multiplexed) 2                     0                   100 The default pool to run query sessions NULL syb_system_pool                    Run To Completion 3                     0                     0 The I/O and system task pool NULL (return status = 0) 

How does the server look when you do ps -ef ?

    This is from top, need more threads / cpus: 18244 rfisher   20   0  537m 169m 166m S      0  4.4   2:13.94 dataserver 

Are there any diags to show what the currently running spids on the various engines are?

Process mode: Top no longer shows a process per engine configured?? Is this a bug or not setup right?

NEW Trace flags have been introduced to change this behavior, these should not be used unless advised by PSE or ENG

     o 1662 When set, exceptions in windows kernel threads will panic the server. (When not set, those threads just exit) o 1664 On Solaris platforms, in threaded mode we normally use our own library to perform async IO on a file system. Setting this trace flag will revert to use the Solaris supplied libaio. o 1665 Enable spinning in the K21 threaded mode scheduler if there are outstanding disk IO requests that have been initiated by this engine. o 7857 Use CS_DEFER_IO in threaded mode. 

Many of the new bugs we are seeing are simply due to behavior of other features not yet up to sniff with this new methodology. For instance, configuration settings are specific to process mode and not used for threaded mode tuning. Here are a few of theCRs filed against this new feature:

     CR 676931  – error when setting the "number of engines at startup" since it isn't supported in threaded mode. ASE supports only threaded kernel mode on the Windows platform. Fixed in ESD #1 CR 671015 - sp_addengine / sp_dropengine are not supported in threaded mode. 

LOB behavior changes in ASE 15.7

   * LOB datatypes can now be used inside stored procedures * LOB locators - * LOB changes in “where” clause * In-Row off-row LOB 

New trace flags for dealing with TEXT for troubleshooting this behavior, many more under cscope

   o 7180 Trace the diagnostics information during compressing or decompressed text/image columns. o 7191 Trace in-row LOB replication information. 

MDA changes

The monitoring scripts no longer need to be run, they are built in now. New tables added for tuning your new server:

   * ASE 15.5     monTableTransfer * ASE 15.7     monDeviceSpaceUsage , monLockTimeout 

Here is a link to cover more details about these tables: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01165.1570/doc/html/mas1311956630416.html

New Features

So many new features have been added since version 12.5. This document barely covers them. Be sure to follow these3 links to review even more of them

   * For ASE 15.5 New Features Guide: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01165.1570/doc/html/jon1253912845085.html * For ASE 15.7 New Features Guide: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01165.1570/doc/html/mas1311808503680.html 

Nonmaterialized, non-null columns and how to add them

Nonmaterialized columns require a default, and the default cannot be NULL. You can include the default by:

   * Explicitly specifying it in the command (for example, int default 0), or * Implicitly supplying it with a user-defined datatype that has a bound default 

What does Nonmaterialized columns mean to the data? Once data is stored in the column it is actually larger than fixed length data type columns.

     Example: alter table titles add alt_title varchar(24) default 'aaaaa' not null not materialized o You cannot use the not materialized with the null parameter. o Use the column default cache size to configure the memory pool for column defaults. 

Overall server performance issues – (most common after upgrade)

   * Review common tuning upgrade techniques * All optimizer hints have to be removed from code or it will run poorly. New optimizer uses actual numbers not assumed like older versions. * Looks through SPs and SQL to verify hints and force plans have been removed * Much higher procedure cache is needed, 2-4 times higher * Update index statistics are now needed instead of just update statistics. If dump / load or or sqlupgrade upgrade method, be sure to run delete statistics first. * Were reorg rebuilds ran after dump/ load upgrade or sqlupgrade method * Were all of the indexes brought over if using bcp or sybmigrate build method * Check for cross platform dump/loads * Was checkstorage ran after migration to verify no reported errors * Sometimes running dbcc gam first can help speed this up. It touches every single page very quickly. Due to some upgrading process being deferred until used. * Did they run sp_post_xpload and sp_indsuspect, after dump/load. If not they could have bad indexes. 

If none of the above are helpful

   * sybdiag -Usa -P -Stestserver * spinmon 2 on on 2 * sp_sysmon “00:05:00” - overall tuning at high level o Use specific MDA queries to drill in later on * sp_object_stats “00:05:00” - looking for object contention * Do we need to configure multiple disk tasks (to improve ASE performance?)  Short answer: Probably not Comments from David Wein posted on ts_server: “There have been a couple of CRs logged this week where ASE 15.7 fails to configure additional disk tasks. These are bugs and the kernel team will fix them. However I'd like to point out that in cases I've seen so far, configuring multiple disk tasks is an incorrect tuning decision.  You should only configure multiple disk tasks if the single disk task becomes CPU bound. You will see a very high thread utilization for the disk task in sysmon (say 85% or more). When that happens I/O latency may increase because the single disk task can't process the completions fast enough, and only then is an additional task the right decision.  Do not add additional disk tasks because I/O busy in engine utilization is high without also checking the thread utilization for the disk task.  High I/O busy without high thread utilization for the disk tasks indicates that the bottleneck is within the host disk subsystem and not within ASE. Example: a case I reviewed had 80% I/O busy for the engines but 20% CPU utilization for the disk task. Adding additional disk task does not help here.  Unnecessary disk tasks may actually hurt performance, just has having more engines than necessary will hurt performance. This has to do with added spinlock contention, CPU context switching, and CPU cache hit issues what come along with having another thread in the mix. -Dave” 

Common CRs

     CR 672829  There is high spinlock contention on network memory pool spinlock. CR 371289  Adaptive Server does not support cross platform dump and load from one Adaptive Server version 15.0 to another. However, pre-15.0 to 15.0 platform dump and load is supported. 

Procedure cache

Procedure cache requirements are 4-6 times higher than what was needed for ASE 12.5.4

The usage of procedure cache and statement cache (if enabled) can be easily monitored in the sp_sysmon outputs of sp_sysmon "00:01:00",pcache

     Procedure Cache Management        per sec      per xact       count  % of total ---------------------------  ------------  ------------  ----------  ---------- Procedure Requests                  0.1           0.1           3       n/a Procedure Reads from Disk           0.0           0.1           2      66.7 % Procedure Writes to Disk            0.0           0.0           0       0.0 % Procedure Removals                  0.0           0.1           2       n/a Procedure Recompilations            0.0           0.0           0       n/a  SQL Statement Cache: Statements Cached                 0.0           0.0           0       n/a Statements Found in Cache         0.0           0.0           0       n/a Statements Not Found              0.0           0.0           0       n/a Statements Dropped                0.0           0.0           0       n/a Statements Restored               0.0           0.0           0       n/a Statements Not Cached             0.0           0.0           0       n/a  Tuning Recommendations for Procedure cache management ----------------------------------------------------- Consider increasing the 'procedure cache size' configuration parameter. 

Spiked CPU 100% - this is commonly due to new hardware in the migration

   * Check threaded mode (SMT - simultaneous multi-threading) * Check for cache (named and procedure) being under configured * Verify the new hardware OS is certified and all required patches have been applied 

If the above are not helpful, collect the following information:

   * sybdiag -Usa -P -Stestserver * sp_compatmode * manual memdump –helpful in seeing what processes are running * spinmon 2 on on 2 * sp_sysmon “00:05:00” - view high level to see place to drill into o use specific MDA queries to drill in later on 

Known CRs

   CR 678639  Closing a connection in ASE may cause high CPU usage. Fixed in ASE 15.7 ESD#1 CR 690665  The cpu utilization may be excessively high when 'enable hp posix async i/o' is set as 0 and 'allow sql server async i/o' is set as 1 on HPIA64 platform. Fixed in ASE 15.7 ESD#1 

Slow SQL or Stored procedure

   * Verify update index statistics are being ran on the table involved - if the SP or table have been narrowed down * Verify the stored procedure involved has been dropped and recreated before going past here 

Sometimes 2-3 stored procedures are being reported as slow

   * Try to focus on one at a time. If you can not find quick similarities. o Create a second case if they need more than one at once. This will help you keep track of each. 

If the common problems above have been checked then be sure to collect:

   * ASE log with last server recycle (errorlog) - verify no stack trace and the disks are configured correctly (directio. dsync) * The server configuration file (servername.cfg) * Collect the SP aka SQL and table DDLs for all tables involved in the SQL/SP o sp_help table as well as create table * Optdiag outputs on the tables involved and sp_showoptstats tablename o sp_showoptstats – is a new feature similar to running optdiag, gives XML * Review how the SQL/SP is acting o Create a script file sql.txt containing these commands: select @@servername select @@version select @@optgoal select @@optlevel go sp_monitorconfig 'all' go sp_cacheconfig go sp_configure 'nondefault' go set statistics time, io, plancost on set option show_lio_costing on set option show_missing_stats on go dbcc traceon(3604) go set showplan on set show_sqltext on go select start_time = convert( varchar(30), getdate(), 109 ) go >>>EXEC SQL -Put your SQL or Stored procedure name here go select end_time = convert( varchar(30), getdate(), 109 ) go Then run with the following to collect an output file: isql -Uuser -Ppassword -Sserver -Ddatabase_name -i sql.txt -o sql.out Collect sql.out and sql.txt from client * Examine optcriteria/optimization level/optimization goal settings. Link to excellent writeup that discusses examining and adjusting these settings, "Optimizer Stability Changes in Adaptive Server 15.x": http://www.sybase.com/detail?id=1080354 This document will be updated to reflect new ESD changes. Note: The meaning of "ase_current" changes slightly beginning with ASE 15.7, see Table 1-1 for details. 

Replication problems and changes

The database version you are coming from will impact the features that both Replication Agent and Replication Server will be able to use.

   * Replication Agent lives inside the database and is version specific * If you have already upgraded your Replication Server to a newer version, be aware o The Replication Agent/Server are still not able to use the new features until the database version has been upgraded. o Version columns have been added to RS 15.6. The new column help to uniquely identifies a replication definition version. o Dynamic SQL statements are a great new feature, as long as you remember to enable statement cache and literal autoparam. NOTE: sizing statement cache, setting to small can hurt performance. Be sure to monitor the statement cache group in your sysmon for removals. o Stack and Core dump 

Known CRs

     CRs (CR 678368 , CR 676313 , CR 677613 ) reporting the same stack trace with module sqm_add_segment. Replication Server core dumps as well. We appear to be trying to go above the partitions that are allocated to RS 

Spinmon

This is a wonderful script that Dave Putz wrote for us. Most people are already familiar with it. Recently Dave was kind enough to go over the output with me. So I will do my best to explain what was found from this.

Here is a copy of the script that was used for the collection. The entry with >>> will need to be modified to the client:

     #!/bin/sh -f  # Usage: spinmon [<minutes>] [on|off] [on|off] # First on|off refers to monitor counters, the second to spinlock counters. # In any case, this script turns both off on completion.  minutes=${1:-1} onoff_monitors=${2:-on} onoff_spinlock=${3:-on} seconds=${4:-00} #password=${PASSWORD:-""} password="" >>>   DSQUERY=dputz1502 export DSQUERY # cat << EOF isql -Usa  -P $password -w132 << EOF  dbcc monitor("clear", "all", "$onoff_monitors") go dbcc monitor("clear", "spinlock_s", "$onoff_spinlock") go waitfor delay "00:0$minutes:$seconds"          /* Sample time */ go dbcc monitor("sample", "spinlock_s", "off") go dbcc monitor("sample", "all", "off") go dbcc monitor("select", "spinlock_s", "on") go dbcc monitor("select", "all", "on") go -- dbcc traceon(8399) -- go  /* Remove this batch if only interested in spinlocks */ -- select field_name, group_name, field_id, value from sysmonitors -- where value != 0 -- go  -- dbcc traceoff(-1) -- go EOF if [ "$onoff_spinlock" = "off" ] then exit 0 fi # cat << EOF isql -Usa -P $password -w132 << EOF -- dbcc traceon(8399) -- go  /* ** The spinlocks are displayed as 'name::id'. name is the ** name passed into ulinitspinlock(). For single instance ** spinlocks id will be 0, for array spinlocks id corresponds ** to the order the spinlocks were intialised in, with 0 being the first. */  /* Get the number of transactions */ declare @xacts float select @xacts = value from sysmonitors where group_name = "access" and field_name="xacts"  if @xacts = 0 begin select @xacts = 1 /* avoid divide by zero errors */ end  select @xacts "Number of xacts"  print "" print "Spinlocks with contention - ordered by percent contention" print ""   select rtrim(P.field_name) + "::" + convert(char(5), P.field_id - (select min(field_id) from sysmonitors where field_name = P.field_name)) as spinlock, P.value as grabs, W.value as waits, (100 * W.value)/P.value as wait_percent, S.value / W.value as spins_per_wait, S.value as total_spins, P.value / @xacts as grabs_per_xact from sysmonitors P, sysmonitors W, sysmonitors S where P.group_name = "spinlock_p_0" and W.group_name = "spinlock_w_0" and S.group_name = "spinlock_s_0" and P.field_id = W.field_id and P.field_id = S.field_id and W.field_id =  S.field_id and W.value != 0 order by wait_percent desc compute sum(P.value), sum(P.value / @xacts)  print "" print "Spinlocks with no contention - ordered by number of grabs" print ""  select rtrim(P.field_name) + "::" + convert(char(5), P.field_id - (select min(field_id) from sysmonitors where field_name = P.field_name)) as spinlock, P.value grabs, P.value / @xacts as grabs_per_xact from sysmonitors P, sysmonitors W where P.group_name = "spinlock_p_0" and W.group_name = "spinlock_w_0" and P.field_id = W.field_id and P.value > 1      /* one because getting the stats gets the spinlock */ and W.value = 0 order by grabs desc compute sum(P.value), sum(P.value / @xacts) go -- dbcc traceoff(-1) -- go EOF 

To execute the script after updating the server name, this will show a 5 minute sample with monitors and spinlock, wait for 30 seconds: ./spinmon.sh 5 on on 30

This is an example of what the output will look like:

   Spinlocks with contention - ordered by percent contention  spinlock                                                                               grabs       waits       wait_percent spins_per_wait total_spins grabs_per_xact -------------------------------------------------------------------------------------- ----------- ----------- ------------ -------------- ----------- --------------------------- Resource->rprocmgr_spin::0                                                                  253690       47732           18 18702   892692400                   30.550337 Resource->rdesmgr_spin::0                                                                   786167       75713            9 5404   409168987                   94.673290 SSQLCACHE_SPIN::0                                                                           178067       10953            6 687     7533243                   21.443521 Resource->rproccache_spin::0                                                                402811       22881            5 234     5370675                   48.508068 tablockspins::2                                                                              19527         416            2 ......removed due to size  Spinlocks with no contention - ordered by number of grabs  spinlock                                                                               grabs       grabs_per_xact -------------------------------------------------------------------------------------- ----------- --------------------------- Ides Chain Spinlocks::93                                                                    404617                   48.725554 User Log Cache Spinlocks::18                                                                140723                   16.946411 Pdes Chain Spinlocks::1128                                                                  134670                   16.217486 User Log Cache Spinlocks::10                                                                117023                   14.092365 Pdes Spinlocks::831                                                                         103868                   12.508189 Ides Chain Spinlocks::15                                                                    103612                   12.477360 Ides Spinlocks::80                                                                          100716                   12.128613 Ides Chain Spinlocks::1                                                                      94689                   11.402818 Ides Chain Spinlocks::106                                                                    94689                   11.402818 User Log Cache Spinlocks::81                                                                 57105                    6.876806 ......removed due to size 

The output above shows us spinlock contention on the procedure cache manager. You can search for this module in cscope or even infobase: "rprocmgr_spin". There are a few bugs reported about this, as well as options of clearing it up.

This can be scheduled, frequency based on need. The command flushes all unused SPs out of cache and will impact performance on first run of the flushed SP: dbcc proc_cache(free_unused)

A trace flag has also been suggested and not quite as effective though, adding trace flag in the RUN_server file: -T758

Sybdiag utility

Sybdiag connects to an Adaptive Server and executes system procedures such as sp_configure and queries to tables like monLicense. It collects operating system and platform diagnostic information by executing commands such as ps, vmstat, and netstat. Then generates a .zip output file comprising HTML and data files that can be unzipped and viewed in a Web browser. The information collected includes operating system and environment data, Adaptive Server configuration and monitoring data, and Adaptive Server files and scripts. Realistically, this is a version of server scraping. Often used with other types of support to get all the needed details on one shot.

The utility itself resides in the bin directory. Example of how to use this command:

     o sybdiag -Usa -P -Srf_sun2_ASE157_4 

This utility needs to have sa_role and mon_role granted to the user or your error will look like this:

     14009 - Lack of Required Role Error: The specified user "sa" does not have both required roles "sa_role, mon_role". Warning: Because the authentication of connection to Adaptive Server failed, data collection type(s) "asecore,aseadd" which require(s) connection will be ignored. 14009 - Lack of       Required Role Error: The specified user "sa" does not have both required roles "sa_role, mon_role". Warning: Because the authentication of connection to Adaptive Server failed, data collection type(s) "asecore,aseadd" which require(s) connection will be ignored. 

To grant the roles:

     o grant role mon_role to username o grant role sa_role to username 

If you do not have monitoring enabled you will hit the following error and a sysmon can not be collected:

     Failed data collection for "Adaptive Server General Performance Information" (ase_sysmon). 14005 - Executing Query Error: Error occurred while executing the SQL query statement "sp_sysmon '00:01:00'". The message returned was "Collection of monitoring data for table 'monEngine' requires that the 'enable monitoring' configuration option(s) be enabled. To set the necessary configuration, contact a user who has the System Administrator (SA) role. 

When enabling monitoring you will also need to set the following:

     o sp_configure 'enable monitoring',1 o sp_configure 'wait event timing',1 o sp_configure 'deadlock pipe active',1 o sp_configure 'deadlock pipe max messages',1000 

After all the above is done, you will get a file new zip file. The file is created in whatever folder you executed the sybdiag utility in:

   sybdiag-rf_sun2_ASE157_4-20120522114503.zip 

At this point you can have the zip file brought in house and review the outputs in Windows. This will work on a Linux system using FireFox.

To unzip the file:

     o unzip sybdiag-rf_sun2_ASE157_4-20120522114503.zip Archive:  sybdiag-rf_sun2_ASE157_4-20120522114503.zip inflating: sybdiag-rf_sun2_ASE157_4-20120522114503/data/asecore/ase_version.xml inflating: sybdiag-rf_sun2_ASE157_4-20120522114503/data/asecore/ase_license.xml inflating: sybdiag-rf_sun2_ASE157_4-20120522114503/data/asecore/ase_cfg.xml inflating: sybdiag-rf_sun2_ASE157_4-20120522114503/data/asecore/ase_nondefault_cfg.xml inflating: sybdiag-rf_sun2_ASE157_4-20120522114503/data/asecore/ase_remote_server.xml inflating: sybdiag-rf_sun2_ASE157_4-20120522114503/data/asecore/ase_script_version.xml inflating: sybdiag-rf_sun2_ASE157_4-20120522114503/data/asecore/ase_mon_cfg.xml inflating: sybdiag-rf_sun2_ASE157_4-20120522114503/data/asecore/ase_cache_cfg.xml inflating: sybdiag-rf_sun2_ASE157_4-20120522114503/data/asecore/ase_pool_cfg.xml .......removed 

This creates a new folder:

     o cd sybdiag-rf_sun2_ASE157_4-20120522114503/ 

To see what you have collected open the following file with Internet Explorer or FireFox:

     o sybdiag_start.html 

You will be redirected to:

     file:///home/rfisher/Cases/sybdiag-rf_sun2_ASE157_4-20120522114503/sybdiag_start.html 

NOTE: sybdiag does not collect Adaptive Server or operating system data for logins, passwords, or user lists, and does not collect information from application database tables.

So many menu options to choose from, it is unlikely you will really need more data than this from the client. Menu Example:

     Adaptive Server Configuration Data Product Version Server Licenses Adaptive Server Configuration Adaptive Server Non-default Configuration Remote Server Configuration Adaptive Server Script Version Adaptive Server Configuration Monitor Adaptive Server Cache Configuration Adaptive Server Pool Configuration Adaptive Server Shared Memory Dump Configuration Adaptive Server Traceflags and Switches 

SySAM 2.0 and ASE 15.x

When SySAM was first introduced in 12.5.x there were many issues with it. First being that a license was not required for the ASE core. The only time a license was needed was when there was a licensed ASE option being used. Second being that it was cumbersome to use and required a license server running all the time.

With the move to ASE 15 we implimented SySAM 2.0. This addressed most of the shortcomings of SySAM 1 and had correct licensing integration with ASE to check for core licenses.

There are 3 license setups.

  • Unserved - This allows for reliable licensing to a small number of ASE's without the need to setup a license server
  • Served - This allows for easy management of licenses across a large number of ASE's as long as the sysam server is running.
  • 3 Node Redundant - With a 3 node redundant configuration a customer will have 3 seperate license servers acting as one in a cluster. This allows for a failover system to the licensing, although it is usually unnecisary.

Pros and Cons

Pros:

  • Unserved
    • Licenses servers are not used at all, no point of failure without a license server.
    • Licenses are flat files that can be checked out directly by ASE, Replication Server, and so on. Crashes do not affect license availability.
    • Simple to monitor which license a product sees and uses because the file is stored directly on the server.
    • No rereads of licenses need to be done since no license servers are used.
    • Licenses on SPDC can be found by hostname making it easier to target problem license files.
    • Troubleshooting only includes the file and ASE configuration. No network communication necessary.
    • Licenses can be upgraded or rehosted individually, which may save time when only a couple products are being migrated rather than many products at once.
  • Served
    • Licenses are all served from one location making migrations to different hardware for products like ASE and Replication Server easier.
    • All licenses can be upgraded at the same time.

Central license pool can be managed directly instead of having to upgrade individual licenses one at a time with unserved

  • Redundant
    • All of the pro's of Served
    • Additional benefit of a failover for the license servers

Cons:

  • Unserved
    • Higher overhead since an individual license must be checked out and placed on each server directly instead of in a central location.
    • License upgrades or rehosts require handling licenses individually rather than en masse; Will take a longer time to upgrade/rehost a large number of licenses than served licenses.
  • Served
    • License tracking is not granular, all license types lumped together for ASE_CORE, REP_SERVER, and so on. Causes issues in understanding what is available.
    • Licenses lumped together for OS platforms as well, not just license types.
    • Ports being communicated with by other applications like ftp, printers, or anything from the network can corrupt a license server instance.
    • Rehosting the licenses means all license files in all instances must have the hostnames changed. Can consume a large amount of time.
  • Redundant
    • All of the Cons of Served
    • Different subnets can cause communication issues that make SySAM redundancy not work even though the servers can ping each other.
    • Various issues with redundancy in the SySAM scripts such as a -local flag preventing license rereads and other flags preventing nodes from coming up successfully in earlier versions of 15.0.x.
    • False errors in SySAM redundancy may indicate an issue where none exists.
    • False reporting may indicate license availability when licenses were not properly reread.
    • Changing a port in the license file means all license files must have the port changed in all instances. Can consume a large amount of time.
    • Crashes may affect license availability as the license will not be properly checked in.

Diagnosing SySAM 2.0 Issues

Items to obtain from the customer:

  • ASE Log file
  • $SYBASE/ASE-15_0/sysam/servername.properties (replace servername with the name of the ASE instance)
  • License files located in $SYBASE/SYSAM-2_0/licenses/
  • Output of sp_lmconfig from an isql session (IQ has a similar stored procedure sp_iqlmconfig) NOTE If the ASE has used the grace period up and will not boot you will not be able to obtain this.

If the customer is using a SySAM server or redundant server cluster you will want the following as well:

  • $SYBASE/SYSAM-2_0/log/SYBASE.log
  • $SYBASE/SYSAM-2_0/sysam status -a

In most situations this will give you all of the information you need to diagnose and resolve a licensing issue.

Common SySAM Error Messages

Invalid Host ID:

  00:00000:00000:2012/05/21 13:35:35.41 kernel SySAM: Failed to obtain 1 license(s) for ASE_CORE feature from license file(s) or server(s). 00:00000:00000:2012/05/21 13:35:35.41 kernel SySAM: Invalid host. 00:00000:00000:2012/05/21 13:35:35.41 kernel SySAM: The hostid of this system does not match the hostid 00:00000:00000:2012/05/21 13:35:35.41 kernel SySAM: specified in the license file. 

This is easily resolved by checking in the license and checking it out with the correct hostid for this particular system.

Syslogs – Shrinking log space

alter database includes the log off parameter, which removes unwanted portions of a database log, allowing you to shrink log space and free storage without re-creating the database. The log off option may be particularly helpful after running the fully logged option for database operations, such as select into, alter table, or reorg rebuild, when the database ends up with extra allocated space that is no longer needed.

Sysmon reports

The newest sysmon report requires certain monitoring tables ot be enabled to collect the needed output. Here is an example of straight out of the box:

     sp_sysmon '00:01:00' Msg 12052, Level 17, State 1: Server 'longspeak_ASE157', Procedure 'sp_sysmon_collect', Line 288: Collection of monitoring data for table 'monEngine' requires that the 'enable monitoring' configuration option(s) be enabled. To set the necessary configuration, contact a user who has the System Administrator (SA) role. ========================================================== Sybase Adaptive Server Enterprise System Performance Report ========================================================== Server Version: Adaptive Server Enterprise/15.7.0/EBF 19805 SMP ESD#01 /P/x86_64/Enterprise Linux/aseasap/2918/64-bit/FBO/Wed Feb  8 7:50:28 2012 ……removed  Msg 12052, Level 17, State 1: Server 'longspeak_ASE157', Procedure 'sp_sysmon', Line 713: Collection of monitoring data for table 'monEngine' requires that the 'enable monitoring' configuration option(s) be enabled. To set the necessary configuration, contact a user who has the System Administrator (SA) role. =============================================================================== Kernel Utilization ------------------ Msg 12052, Level 17, State 1: Server 'longspeak_ASE157', Procedure 'sp_sysmon_kernel_threaded', Line 94: Collection of monitoring data for table 'monEngine' requires that the 'enable monitoring' configuration option(s) be enabled. To set the necessary configuration, contact a user who has the System Administrator (SA) role.
Former Member