SAP ASE Repository

A repository for SAP (previously Sybase) ASE stored procedures and scripts
Please contact saplump at yahoo dot com to contribute, to report problems with what's available, or to suggest something new

Please feel free to use anything on this page. All I ask is that you don't pass it off as being authored by yourself
If you have a project that will make use of one or more of these procedures, which you will need to hire extra staff for, would you please consider contacting me to see if I'd be interested in the work?
Find out more about Raymond Mardle

Page Last Updated : 10th Mar 2018 : sp__optdiag for ASEs 15.5, 15.7 & 16.0 and sp_rpm_summ_stats can / will now output partitioned stats in condition creation order instead of partition ID order for hash and roundrobin partitioning, which can wrap around the int range
5th Mar 2018 : sp__optdiag for ASEs 15.5, 15.7 & 16.0 and sp_rpm_summ_stats can / will now output partitioned stats in condition creation order instead of partition ID order for list and range partitioning, which can wrap around the int range
1st Mar 2018 : sp__optdiag for ASEs 15.7 and 16.0 now executed as owner
 5th Feb 2018 : Performance update made to sp_rpm_summ_stats

Cheating With Statistics in SAP ASE : A PDF of a presentation describing the statistics based system procedures on this page, and how to use them to "cheat" where statistics updating is concerned (31 Dec 2017)

: An updated versions of Kevin Sherlock's procedure to give the same output as optdiag but using a stored procedure. Updated 21 Jun 2017 mainly to to handle column groups containing columns with an ID greater than 256. Updated 18 Aug 2017 to output uni[var]char as strings when @elo = N. Updated 31 Aug 2017 to make them even more @elo & other tweaks (the procedure was over 3.5 times faster than optdiag in both ASE 15.5 and 16.0) - for ASE 15.5 (10 Mar 2018 : partition creation order b), 15.7 (10 Mar 2018 : partition creation order b) or 16.0 (10 Mar 2018 : partition creation order b)

sp_thresholdaction : In 2004 I was working for a company that was having problems with their transaction log sequence being broken when they tried to load production log dumps into a standby system. It was traced to an issue where the same log threshold was crossed multiple times within a very short time frame, causing the same file name to be reused for successive transaction log dumps. I amended the procedure to handle these problems, wrote an article that appeared in a 2004 issue of the ISUG Technical Journal, and made the procedure available to the ISUG community. I have made further changes for this version to make it more flexible. It is valid for all versions of ASE from 15.0 onwards (and probably pre-ASE 15 too), but it will need some customisation for your environment, depending upon whether milliseconds are to appear in the file name and where the files are to be stored - (12 Jun 2017 : print and error handling changes)   updated draft of the article

sp_rpm_tablesize : If anyone has ever spent time administering ASE, they have probably written a procedure to get table size information for more than just one table at a time using sp_spaceused. This is my take on such a procedure, but with some useful (for me) added abilities. The output can be sorted by any of the columns : by use of the @sort parameter. The results can be whittled down using a specific table name or pattern as you'd expect, but also by restrictions on any one of the columns : by use of the @res_type and @res_value parameters. The name of a table containing specific tables to output the details for can be supplied : by use of the @tab_list parameter. A simplistic check can be done to identify tables that have a row count that does not match an expected data size when the average row size is applied : by use of the @check parameter. These versions use "unsigned bigint" for the row counts, so they cannot be used in pre-ASE 15.5 servers. Although the one for ASE 15.5+ servers can be used in ASE 16.0 servers, a new methodology for getting size information means that it is not totally accurate. Consequently, use the one for ASE 16.0+ servers to get accurate results. ASE 15.5+  (14 Mar 2017),  16.0  (15 Mar 2017)

sp_generic_device_free_space : For ASE 15.0+  (13 Feb 2017)

sp_rpm_summ_stats : In my 23+ years of using ASE (with its different names - does anyone else remember Sybase SQL Server 4.9.2, and were you as puzzled as I was by the jump from four to ten?) I seem to have spent a significant proportion of my time investigating statistics. This is one of the procedures I wrote to help with that. It summarises statistics without outputting the volume of information that {sp__}optdiag outputs. It uses "unsigned bigint" for the row counts, so it is only for ASE 15.5+. Like all the procedures I write from scratch, use "help" or "?" as the first parameter to get information on how to use it and, in this case, an explanation of some of the output. Updated 14 Sep 2017 to have partition summary information and individual row counts for partitioned tables. For ASE 15.5+  (10 Mar 2018 : partition creation order b)

sp_rpm_custom_stats : The procedure above and this one were written due to issues seen with joining multiple multi-million row tables. An article I wrote for the Third Quarter 2006 ISUG Technical Journal (P1 & 2  P3 & 4  P5) discuses the issue in more detail and what was done to try and solve the problem. Writing this procedure to just change the requested steps for tables that could not be partitioned was much easier than modifying it to handle the various statistics attributes that can be changed in ASE 15.7+ tables that can be partitioned. There is a chance that it will not behave correctly for some combinations of table types, index types and partition types. If so, please let me have the details and I will endeavor to sort the problem out. As I found out when I was writing the original version of this procedure, statistics can stand up to some terrible mis-handling. If there are problems, delete the statistics and run an update statistics. The procedure should work for ASE 15.0+, where only requested step and density values can be changed if pre-ASE 15.7.  (31 Jul 2017 : tidy up help and change @silent default to N)

sp_rpm_shuffle_stats : Consider this scenario. A table in a database contains sets of rows that are grouped by a unique date. Each week day, a new set of rows is added to the table for that date; and when that day is over, the rows for, say, 28 days ago are deleted. The database contains many such tables, many of which are very large, and there is near constant activity against the database during the week, so it is not possible to update the statistics every day to pick up the new date's rows. At the most, the statistics for the tables in the database are updated one a week. However, the stats updating maintenance window is sometimes shortened by other activity and so not all of the tables in the database get their statistics updated some weekends.  In ASE 15.7 ESD#2+, it is possible to use "out of range" but that will only really be accurate for the first day's data added after the statistics have been updated. Each subsequent day will lead to increasingly incorrect assumptions for a specific new date. This procedure shuffles statistics for the grouping column of such a table. Running it after the rows, say, 28 days ago have been deleted will result in the pair of statistics entries for that date being removed from the start of the statistics, each pair of entries for the other days moved down one pair, and the last entry's pair being changed to have the new date and the weight from the first entry. It relies on there being two statistics entries for each unique date (with the first entry of the pair having a NULL value and a zero weight), and the weights for each date (and hence the number of rows) being approximately the same. It is the process that was mentioned under Rolling Dates on page 5 of the ISUG journal scanned for sp_rpm_custom_stats - which I never got to implement for the company that employed me when I wrote the article. In ASE 16.0, using a datatype of date resulted in two stats entries for the oldest date but one entry of <= for the other dates, so columns with a datatype of date are not valid for this process. Use of bigdatetime, datetime and smalldatetime produced suitable stats entries - when sufficient steps were allowed. For ASE 15.5+  (26 Oct 2017 : better bigdatetime handling and elapsed time added when debug used)

sp_rpm_copy_stats : Consider this scenario. The schema for tables in a database are usually altered using the following method : 1) the existing table is renamed 2) a new version of the table is created (which is usually simply adding new columns to the end of the existing schema) 3) the data in the renamed table is inserted in to the new version of the table, with only the new columns having new data that is not in the original version of the table 4) indexes are created on the new version of the table 5) index statistics are updated for the new version of the table 6) if any problems at any prior stage, then the next step is not done and instead the new version of the table is dropped and the table is renamed back to its original name 7) if there were no problems, the old table is dropped. For a small to not too large table, this process doesn't take much time. For a large table with many indexed columns, the whole process can take a long time. Because the data in the existing columns hasn't changed, the statistics for those columns is valid for the new version of the table as well. This procedure copies the statistics (summaries, values and weights) for the existing table and concludes with there being statistics for the new version of the table. In the scenario above, it is simply a matter of changing the object ID to be that of the new version of the table. However, stage one of the procedure can also automatically handle existing columns changing position in the new version of the table, as long as it retains the same name in the new position. If any columns with statistics change position and / or name, then the procedure can also handle that but it needs to be given the current and new column names in the parameter @col_manual in the format '<original>=<new>[ |,|;]'. For columns that change position, that involves changing the colidarray values to have the new ID values for the moved columns. Steps 1), 2) and 3) would be as above. Step 4) would now be the execution of this procedure. Step 5) would be the creation of the indexes, which updates the statistics for their leading columns, unless that is stopped (I don't know how much time is added to index creation by doing leading column statistics updating). Step 6) would be updating statistics, but only for those new columns that are in an index as a non-leading column. Step 7) would be the old 6) and step 8) would be the old 7). Stage two to handle columns changing datatypes (as long as its possible to explicitly convert between the current and new datatypes) is complete. Stage three to handle changes to partitioned tables is complete and it can also handle indexes changing if index ID is ever stored in sysstatistics. Stage four to handle cross-database and cross-ASE server stats copying is complete. For ASE 15.5+  (25 May 2018 : extended to handle cross-database and cross-ASE server stats copying, and improve table and user checking)  (14 Sep 2017 : example 1 output)  (14 Sep 2017 : example 1 [sp__]optdiag output)  (14 Sep 2017 : example 2 output)  (14 Sep 2017 : example 2 [sp__]optdiag output)

sp_rpm_append_stats : Consider this scenario. The same situation as for sp_rpm_shuffle_stats but the tables are not fully populated, and new sets of data are added every day but the oldest set of data isn't deleted. This procedure appends a new pair of histograms for the first date more recent than the most recent one with stats, and massages the weights for the existing stats and the information in the summary row. For ASE 15.5+  (31 Oct 2017)

ASEBCPipe.ksh : Unless you are the DBA in both ASE servers (for example to set up for and use proxy tables), the simplest and best way to copy data between tables in them is to use BCP. If you stick to the SAP documentation, then a file has to be used to store the data in between the BCP out and in. However, it is possible to use a pipe to join the OUT and IN together and do both simultaneously, which takes about half of the time of doing both separately. Many moons ago, between employments, I had ASE 12.0 and a Korn shell interface on an old laptop, so I wrote a Korn shell  script to use a pipe to perform this type of copying between two already existing tables, plus copy to / from a compressed file. I have now updated it to handle ownership, allow the destination table to be created, drop it first if it is in a specific database, and be more selective about which indexes are dropped and re-created. The new version of the script was updated on a virtualised RHEL 7.4 running ASE 16.0 SP03 PL02, so it uses Linux line terminators. Use unix2dos or something other than notepad to read it in Windows. For ASE 15.5+  (1 Feb 2018)

MultiASEBCPipe.ksh : The above Korn shell script only operates on one table at a time. This Korn shell script can be given a list of up to 99 tables and it will execute the script above for each of them, in the background and simultaneously (which could overload the two ASE servers, so a maximum concurrent can be specified). All of the tables can be processed in the same way. Or, flags can be told to only operate on some of the tables in the list using inclusion or exclusion lists of tables. The new version of the script was updated on a virtualised RHEL 7.4 running ASE 16.0 SP03 PL02, so it uses Linux line terminators. Use unix2dos or something other than notepad to read it in Windows. For ASE 15.5+  (17 Jan 2018)