SYBASE UTILITIES TOOLKITFree Perl Tools For Sybase |
This package contains a bunch of sybase related utilities written in perl. These utilities are designed to help make your life simpler - so enjoy them.
If you make changes to these utilities, find bugs, or if you have utilities to contribute, please contact the author. The current location to contact the author is at www.edbarlow.com.
The toolkit also includes several perl libraries. These libraries will be installed by the installation process into your perl path. The libraries include
Doc Module - The package that produced this documentation Webify Module - Forms based cgi system (not used) LogFunc Module - Log File Parser Sybase::Release Module - Sybase Release Manager. Sybase::SybFunc Module - Sybase Function Library
This package was written by Edward Barlow and is distributed from the Ed Barlow's Sybase Stuff website (www.edbarlow.com).
The distribution point for this package is The Sybase Shareware Site at http://www.edbarlow.com. All material in this package is Copyright © 1998-2000 By Edward Barlow, All rights are reserved. Dont use them in commercial packages without permission, but please redistribute them as you like for your own work.
This program is free software. You can redistribute it and/or modify it under the same terms as Perl itself.
Up to date, on line documentation can be found at http://www.edbarlow.com/document/utilities/index.htm .
Documentation is stored in html format in the bin/html subdirectory.
As with all perl code, you can use perldoc to get instant documentation/help. perldoc adduser.pl will give you the usage of the adduser.pl command (you should be in the utilities directory though).
These utilities work under both UNIX and NT. My NT test bed uses MKS toolkit, a wonderful UNIX like shell (highly recommended), but all these utilities can be also run at the dos command prompt.
The software itself can be found at utilities.tar.gz. Follow installation instructions to install.
These utilities require perl 5.002 or later. Unix Source can be found at www.perl.org. An NT version can be found at www.activestate.com.
On NT, you will need to associate perl with .pl extensions (done in the default NT configuration).
Sybase::Dblib is required as a perl add on.
A few NT specific utilities will use Win32::Process Win32::Service and Win32::Shell. These need not be installed unless you wish to run these NT utilities - they start with the letters nt.
The add on libraries will need to be installed by hand. Use ppm.pl on NT and follow normal download instructions on CPAN to install the UNIX version.
Your version of perl must be in your path and your environment variable $SYBASE must also be set.
The free extended stored procedure library (also found at http://www.edbarlow.com) MUST be installed on your sybase servers. These stored procedures are used throughout this code so be sure the latest version is installed.
Download the file. Rename the file to have a .gz extension if it does not have one after you download it.
Uncompress the download file using pkzip (windows) or gunzip utilities.tar.gz followed by tar xvf utilities.tar on UNIX. This creates two subdirectories, named bin and lib.
Change to the bin directory and run configure.pl. This set hashbangs so that commands can figure out which command interpreter needs to be run. The program also runs the utility clean_ctrl_m.pl -D., which removes control M's from the source files (the files are developed on nt so this is required), sets file permissions, and copies the libraries from the lib subdirectory into your perl path.
The GetPassword library can be customized to your own specifications. Its pretty simple. If you wish to use the built in password file, just edit the password.cfg file in the source directory into the format SERVER/LOGIN/PASS.
You may wish to add the utilities directory to your path.
I like html as an easy, platform independant way of showing output. Many of these utilities output html if the -h option is used. This often is the prefered mode of output (you get color, formatting etc...) and it sure beats text files. You can look at these files by pointing your browser to them, or install your own web server (apache is free from www.apache.org) and to place output files in a location that web server knows about to view it very nicely.
adduser.plutility to add users and logins |
This perl script creates / drops logins & users. It works in a set of databases (ie. you can pass in a wildcard for the db - but you probably dont want to pass in % or the user will be added to tempdb etc... It creates the login only if necessary. The user is added to public if the group is not passed. It deletes the login only if -x is passed - so you could invoke the script multiple times with multiple db% lists to get a useful setup.
./adduser.pl [-dx] -USA_USER -SSERVER -PSA_PASS -DDB_LIST
-uNEW_USER -pNEW_PASS -gNEW_GROUP [ -rDEF_DB -fFULLNAME ]
DB_LIST may include wildcards
-d debug mode
-x remove stuff only
You may pass a sybase wildcard to DB_LIST like xrm% and it will only work in those databases
You may ignore -U and -P options if you have set up password file
It will print ugly messages if both the NEW_GROUP and the public group are not available.
It will fail if trying to remove login that has objects or that is actual db creator (not just owner). You must run sp_changedbowner to get it to work.
alldb.plloop through your databases utility |
Run a program on every db in a server or on all objects in a database. You can select a subset of databases to run in and can run on objects by type (tables...).
Usage: AllDb -sne -TTYPE -SSRV -UUSR -PPASS -DDB SQL_STMT
Log in to SRV and runs SQL_STMT in each database you can use. This program may also be used to run a proc on each object in a database.
B<-s> - no system databases B<-D> - Database mask (include sybase wildcards) - Default = % B<-T> - Object Type - Run "SQL_STMT ObjectName" for each object of type -T B<-e> - echo cmd to screen B<-n> - dont exec command - just print it (sets -e option)
The parameter to -T should be U (table), P (proc), TR (trigger)... as per the definition of the type field of sysobjects.
Example: AllDb -SX -Usa -Ppass exec sp_helpdb will execute sp_helpdb in all your databases.
Example: C
Output is tab separated and no headers are printed (its not isql you know)
AllSrv - AllSrv and AllDb can be used in conjunction easily
Run a program in all your servers. This is useful with the other utilities provided in this directory. It requires that the run utility take -S/-U/-P parameters. The server should be in password.cfg.
In other words... if you wish to run a program to remove user paulr on a server you might type
but with this utility, you could run that command on ALL your servers in one fell swoop with:
As you can see, the -U,-P, and -S parameters are added automatically
Generic program to run on all servers. If you type
you will end up executing
for all the servers in your password.cfg file.
Parses through sysaudits and gives you pretty output. This relieves the signifcant pain in the rear end to look at audit info. Since sybase auditing is brain dead in its timings (who decided to not audit end times of batches), the summary timings that this program calculates are really nearly useless - they are based on the difference between the statement and the time the next statement was run.
Of use however, is the parser, which does things like parse down strings (in quotes) to nothing and remove parameters from stored proc text, which makes your output times summarized better.
html output is the preferred method of viewing the logs as tables are perfect for this kind of thing.
Simply Backs Em UP
Bcp files into a server. The files should be created with bcp_out.pl. The files are in character mode with a delimeter of two tildas.
Bcp all tables out of a database and into flat files The files can be read with bcp_out.pl. The files are in character mode with a delimeter of two tildas.
bcp_out.pl [-dx] -UUSER -SSERVER -PPASS -DDB_LIST -oOUTDIR Tbl Tbl Tbl
Uses cksum program to compare checksums in a directory (and its subdirectories) with a static data file. A great way to identify programs that are bad or out of date between a release and an output directory. Ignores files with extensions .err, .log, .sample, .env, .cfg, or with digits as their extensions.
Create a 'gold' area of your software and run the checksummer on it to create an output file. You can then compare the output file to other directories to find what is different. eg:
Usage: check_app_cksum.pl -DDirectory [-oOUTFILE|-iINFILE] -RPrefix -f
clean_ctrl_char.pl FILE FILE FILE ...
Clean up control characters from files.
Filter that removes new lines that can be created when moving DOS-UNIX. Can be used to operate on a file, directory (with subdirectories), or stdin.
The program is simple.
Prints a full server configuration report. This is intended as nice documentation in case your server crashes or has a problem. This program should produce virtually everything about your server that you might care about except user object DDL.
USAGE: copy_db.pl -sTOSRVR -uTOUSR -pTOPASS -DTODB -UFRUSR -PFRPASS -SFRSRVR -DFRDB [-XFQH]>
-x : debug mode
-f : Dont ask for confirmation of copy - just run copy
-r : Just print stuff summary report - dont do copy
-q : Run Query Into Table (must have -b defined)
-b : Target Table (must have -q defined)
-h : html output
-t : delete target table data first
Copy identical objects between databases.
Copy database data to another database. Decipher the table layout and will only copy if the layouts are identical. It will handle identity inserts as needed. Will truncate target tables unless -t is set. Can take query with -b/-q and load it between servers.
Extended stored procedure library
This very simple perl script creates a group named super_user_group and a group read_only_group in all your databases in a server. The super_user_group is granted select,insert,update,delete on all tables and views, execute on all procs. The read_only_group is granted select on tables only.
It is kind of nice to have these groups around and then you can manage who has access to what based on these things. Of course, if you change ddl (drop/add stuff) then the permissions will be lost and you will need to add them back by rerunning this proc.
note database can be a wild card
Output a set of insert statements that will rebuild the data in a server.
Creates a cross tab report based on a query. This reformats your output in a nice concise manner. For example, say you wished to run a report on each database in your server and generate a report that looks like:
You would first write a query that looks like
To Return
Of course this only works in one database. You then run your cross tab creator and it will create the crosstab.
-D - Database list (may have wildcards)
-Q - Query List
-R - Rows
-C - Columns
-O - Output
-i - integer output (sum em up)
-h - html output
crosstab.pl -D% -Qsp__helpuser -R1 -C0 -O4 -SSYBASE_QA3 -USYS_INSTALL -PPASSWORD
crosstab.pl -Dxrm_db,xrm_sod% -Qsp__objprotect -R1,2 -C3 -O4 -SSYBASE_PROD -USYS_INSTALL -PPASSWORD > xxx.csv
Usage: custom_rpt.pl -USA_USER -SSERVER -PSA_PASS -RREPORT_NUM
Runs predefined crosstab reports. Requires extended stored procedure library.
Compare the number of rows in two db's and print diffrences. Note that only the number of rows are compared, not actual data differences.
Requires extended stored procedure library
Simply Dbcc Em UP
Compares two databases. Checks the following things:
dbcompare.pl [-xh] -FFile -Ssrv -Uusr -Ppass -Ddb -ffile -ssrv -uusr -ppass -ddb
If a server is passed, it will connect to that server and use server data. If a file name is also passed, that data is written to the file. If only a file name is passed, the file is read and used as the data source. This allows you to compare stuff at remote sites to what is supposed to be in the database.
Works only on DBO Objects. Might have some bugs with non dbo object data (but i dont think so).
Check out your server! Should audit as much as it can!
delete all tables in a database
might blow up on ri
Script to extract and prettyprint dependencies
Script to build output file
depends_analyze.pl [-I \$SYBASE] [-S DSQUERY][-U user][-P password][-D DATABASE][-H directory]
This script extracts relationships (dependencies) for a server. This can be used for impact analysis (if i change a what happens). Works to help understand all relationships between triggers, procedures, tables, views.
Output File Named is created in directory named depends.\$DSQUERY
Hides password from most ps sniffers.
Handles references to objects in other databases.
This script is not built on sysdepends, the unreliable table, but on analysing the objects' texts.
Smart enough to follow trigger only if apropriate.
Handles the problem with sp_helptext sometimes truncating spaces.
Constraints Not Implemented.
Should be a dbm output file.
Release Manager - Loads database based on file ddl. The ddl can be stored in control files to ensure ordering of inserts. The utility manages tables by archiving the data and then copying it back. This is a great tool to manage upgrades of databases.
Usage: do_release.pl -UUSER -PPASSWD -SSVR -DDB [ FLAGS ] -VDIR/-c CTLFILE/PROCEDUREs
Installs scripts into your server, preserving data for tables. The scripts to be installed are based on a control file located in the same directory that contains the scripts (default for -c is do_release.ctl). If neither -V and -c procedures are read from the command line. The DB parameter can contain sybase wildcards.
-s# - start at proc # (between 1 & 999)
-e# - end at proc # (between 1 & 999)
-N - New Objects Only
-n - noexec do not run the isql
-r - ignore rules (.rul or type=R) and defaults (.def or type=D)
-T - install stored procedures only (.prc or type=P)...
-b - batch mode - no waits or delays (and dont die on error)
-x - batch mode - no waits or delays (but do die on error)
-s - Dont Save Table Data
If u do a use XXX statement as a batch, your db will be set to that database
Self document your database! Uses source code and the stuff in the system tables.
Retrieves from server:
procedures - uses sp__syntax
tables - uses sp__helpcolumn & sp__helpindex
Retrieves from code directory and its subdirectories:
- object & type based on file endings (must be in in %okext hash)
- object comments excluding predefined strings in @ignorestrings
- code directories can be ; separated
Pretty good output. Kind of loses shape if the comments in your ddl are ugly. They probably are though. One concept here is that you can document stuff inline FOR PURPOSES of this type of utility. Reformat your stuff so you can see what your tables do etc... Simple and Somewhat useful.
drop all objects from a database
does not handle failure well. RI issues?
Extract DDL for object and print it.
Fixes up / sets db options. Right now it will
change single user to multi user if data and log on same device -> set truncate log on checkpoint Select Into/Bulk Copy -> set truncate log on checkpoint if truncate log on checkpoint -> set Select Into/Bulk Copy
This program acts as driver to the analyze.pl program, which performs a showplan analysis on a single stored procedure. Runing this program will analyzes a whole database. Output can be ascii or html, but the program runs best if used with html output (that is how it is tested and html lets me do pretty things).
Information is summarized into a nice web page which gives a synopsis of your database. This includes analysis that helps you identify table scans. You can drill down into your procedures and see detailed analysis of these procedures.
This procedure loops through all stored procs in a db and runs the analyze package on each of them. Output is placed in files in a subdirectory SERVER/DB in either the document area (if -h is passed) or the data subdirectory of the current directory.
An index.htm file is placed in .../analysis/SERVER/DB with appropriate pointers to the other files created. If analysis is not in your tree it is created (must have write permissions) as are the subdirectories.
The extended stored procedure library must be installed
User defined data types should be set up in the full_analysis.dat file. What this means is that the program will substitute values for parameters to each of the stored procedures it runs. It does this by making something up. For strings, it usually uses "str", unless it is less than a varchar(3), in which case it will use "X". One problem here is user defined data types. Obviously the program needs to know whether the type is a string... Sometimes your stored procs expect certain values for particular parameters. You can set these values also. After you do a run, you will see errors, which can be fixed by editing the full_analysis.cfg file. As you work with this tool, you will build a database of default values for your procs.
This procedure loops through all stored procs in a db and runs the analyze package on each of them. Output is placed in files or can be html.
Output is in SERVER/DB in either the document area (if -h) or data subdirectory.
You may ignore -U and -P options if you have set up password file
Extracts comments of the style /* */, --, and #
Pretty Interfaces file entries. Works on both unix and nt. On NT it reads $SYBASE/sql/ini. On unix it reads $SYBASE/interfaces. If you pass an argument it only shows server with that string in it.
get_interfaces.pl [search_argument]
This program filters log files. It can filter out garbage messages based on data it reads from an exclude file, which is a list of messages and what to do with them (pretty simple). It can also filter to only print messages from today or from yesterday & today. Since dates are of many formats, you pass in a format string for the date.
A patern file can be used. This file is used to only see new messages (after the patern). The patern file is of the format [byte_count\tstring]. If the string is in the file at that byte count (ie it is not a new file), then the filter will only print messages after that line. If not it will read the whole file. You can use this to see only new messages (messages since the last run of log_file_filter).
Usage: log_file_filter -i infile [ -e file -s pat|pat -p file -f fmt -odyt ]
-d - run in debug mode
-b - ignore blank lines on input
-i file - input file name
-e file - exclude file (see program header for rules)
-s pat|pat - patern that all output lines must have
-p file - patern file containing print print after patern
-P file - same as -p but does not write patern file
-o - print a message if no rows are found
-f fmt - format for date strings
-y - print yesterdays messages
-t - print todays messages
-D dir - run on all files in directory (cant also use -i or -p)
-T - with -D only files modified today
-Y - with -D only files modified yesterday
-S pat - with -D filter on files with patern in name
-m cnt - only print up to m lines
The format string may include spaces, /, mm, yy, dm, dd, mon. dd will prepend a 0 to days 1-9, ds will prepend space to 1-9
To read the whole sybase errorlog
log_file_filter -i $SYBASE/install/errorlog
to read errors in that errorlog
log_file_filter -i $SYBASE/install/errorlog -e sybase10.excl
to read errors since the last run
log_file_filter -i $SYBASE/install/errorlog -e sybase10.excl -p tmp
to read todays errors
log_file_filter -i $SYBASE/install/errorlog -e sybase10.excl -t -f"yy/mm/dd"
to read todays and yesterdays errors
log_file_filter -i $SYBASE/install/errorlog -e sybase10.excl -yt -f"yy/mm/dd"
and finally, to read only today & yesterdays messages that occurred since the last
Currently, the following files can be used for filter purposes:
This probably needs some explanation. Fundamentally the program takes an input file (from -i) and prints it after filtering. It uses an excludefile to define what is an error (actually the file defines what is ok). You can also filter on recent messages using the -t or -y flags in conjunction with the -f date format option (log files have different date formats and you need to specify what you want to search for).
Another major way to search through this file is to use a patern file (-p). If a patern file is specified, only lines after the patern (if found) are printed, and the patern in the file is updated with the last line from the errorlog. This is useful to see errors that have occurred since the last time you ran the filter.
The exclusion file is self documented but basically uses lines of of the format XXX=patern and excludes based on exclusion type as follows:
The -D option is a new feature, allowing the browsing of error log directories. This is very different from normal browsing. The -t and -y flags still work, but they refer to files from today and yesterday. The -s flag operates on file names (not patern strings in the file).
This general parser should be good enough for most patern parsing of log files. Output is to standard output.
Converts all * characters to a - to deal with perl wildcarding. This should only be noticable on the output prints
Note this code requires perl 5.002 or later to run
Usage: nt_service.pl Usage: nt_service.pl service Usage: nt_service.pl START|STOP service
SERVICE: Alerter SERVICE RUNNING SERVICE: Apache SERVICE RUNNING SERVICE: COM+ Event System SERVICE STOPPED SERVICE: ClipBook Server SERVICE STOPPED SERVICE: Computer Browser SERVICE RUNNING etc...
Show NT services. Filters service if service passed (one argument). Starts & Stops services if second argument is STOP or START.
show all services
start service
stop service
filter services
show running/stopped services
Shutdown an NT system. Only works if you have administrator priviliges.
Print Roles By Server/User In Nice Format. Has a variety of ways to decode and print role info. Servers must be defined in password.cfg to work. Requires password file to be set up.
***********************************************************
* SA OUTPUT *
***********************************************************
kent SYBASE_INT
robp SYBASE_NT
chikki SYBASE_DEV
TestTrigger SYBASE_DEV
SYS_OPER SYBASE_VALQA SYBASE_RRDEV SYBASE_QA SYBASE_DEV
SYBASE_PROD SYBASE_INT SYBASE_NT SYBASE_Y2K
paulr SYBASE_VALQA SYBASE_RRDEV SYBASE_QA SYBASE_DEV
SYBASE_PROD SYBASE_INT
sa SYBASE_VALQA SYBASE_RRDEV SYBASE_QA SYBASE_DEV
SYBASE_PROD SYBASE_INT SYBASE_NT SYBASE_Y2K
psr SYBASE_INT
SYS_INSTALL SYBASE_VALQA SYBASE_RRDEV SYBASE_QA SYBASE_DEV
SYBASE_PROD SYBASE_INT SYBASE_NT SYBASE_Y2K
Shimizu SYBASE_INT
yury SYBASE_VALQA SYBASE_RRDEV SYBASE_QA SYBASE_DEV
SYBASE_PROD SYBASE_INT SYBASE_NT
This perl script runs a query in two servers and then compares the output. This could be quite useful in a variety of situations, specifically when you want to regression test a new procedure.
Query does not care about the order of the output
Unless you use the -h (html flag), output columns are separated by the pipe | character which might be slightly confusing.
Usage: query_compare -X -Uusr -Ssrv -Ppass -uusr -ppass -ssrv -Ddb -ddb -Qquery
If you ignore the -s parameter, it is assumed that you want to use the same dataserver for both sides of the comparison.
The following query compares servers SYBASE_QA and SYBASE_INT in database xrm_db to see which tables are in one server but not in the other
query_compare -SSYBASE_QA -sSYBASE_INT -Dxrm_db -Qselect name from sysobjects where type="U" uid=1 order by name
The following shell program compares data in $1 on two servers
This procedure builds missing indexes in a server. It relies on a file extract from another server to understand what indexes to rebuild. The magic of this proc is in its error handler, which understands the errors that can be generated by index generation and in its duplicate row processing. You can rely on the fact that when this program is done, your indexes will be correct. Use it to maintain indexes between identical servers.
Note: this is not a script to recreate existing indexes. This is a script that corrects indexes in a server that might be incorrect by making them like the indexes in a file (which can be extracted from another server).
The extract file is is just index create scripts, 1 per line. For syntactical clarity, it is recommended that the extract file be based on the extended stored procedure sp__revindex, which is found in Ed Barlows extended procedure library. The program can be used to extract output into a file (using -o) which can later be run into another server (using -i). Duplicate rows are cleaned up if the -c option is used
requires sp__revindex (extended stored proc library)
Change hashbang lines - #! - at top of files to legit value for perl. This protects you in case of perl not being in your path
or
Reset perl on first or first 3 lines as necessary. If the first line starts with a hashbang #! then that word is reformatted as necessary. If the first 3 lines looks like:
Reverse engineer objects to individual files. If you dont pass a set
rev_db.pl -USA_USER -SSERVER -PSA_PASS -DDB_LIST -OOutDir [-E] [objects...]
Reverse Engineer To Files... Extensions based on object name
This is a customized package. Installations must be customized for each server / environment you wish to work with.
setup.pl ENVIRONMENT
For a list of valid environments, run the command without any arguments. Valid environments must be setup by editing this script. See the CUSTOMIZATION section. You may run with the environment ALL which runs in all environments.
This program lives in the root directory of the patch release. Database objects get installed based on the directory right above them.
You must edit this script to define what the environments are. Specifically, you must add your environment to the perl hashes to identify installation info.
Target directory for installation
DSQUERY of server for db objects
LOGIN of server for db objects
PASSWORD of server for db objects
Use of environments allows you to install into remote directories and servers transparently without specifying everything on the command line.
Requires do_release_func.pl and syb_func.pl to be installed either in the current directory, the subdirectory utilities, or the LOCAL root.
Should upgrade platinum and gold whenever a release is made
Should do a better job checking for existing patches
Perhaps we should have a facility to see which patch upgrade we are at from
Perhaps it should keep track of individual object upgrades in its own database
Analyze will analyze ddl by using showplan. It can runs the program in an optional transaction and rolls back the query when it is done. Its primary purpose is to reformat the showplan output, It produces color coded output files that contain query results, statistics io, showplan information, and readable summary output.
Analyze can output html directly, which is the best way to view results.
The best way to tell you what this tool does is to demonstrate. If you are a unix user, the files example1, example2, and example3 contain code/stored procedures in them that can be run by the run_examples script (requires pubs). Please look at the example* files after you run the script (change the script so it has appropriate passwords. Thats all there is to it.
Note this package requires perl 5.002 or later. The software will work under all versions of sybase. The software requires sybperl.
USAGE: analyze [-dL] -o FILE -U USER -P PASSWORD -S SERVER -D DATABASE [ -F FILE or -C CMD ]
Showplan Analyzer Copyright (c) 1996-8 by Edward Barlow
-d : debug mode
-L : do lock analysis. The session is trapped inside a 'BEGIN TRAN' and locks are measured. The tran is rolled back after. This aborts on operations that cant handle a tran (select into...), but should be set by default.
-h : html'ize output
-n : set noexec on - will work poorly if running a stored proc
-i : show statistics io
-l : show lock stuff
-o : output to this file as well as to screen
-t : show procedure text (proc name is first word after exec) -r : show raw showplan output
-e : show sql the analyze program executes
sub usage()
sub message_handler_mine()
sub parse_showplan_output()
sub parse_stats_io_output()
sub lock_print()
sub normal_print()
sub results_print()
sub io_print()
sub output_print()
sub format_page()
sub title()
sub print_toc()
sub db_colorize_sql()
sub row_output()
sub format_output_row()
sub reset_tbl_row_vals()
sub sql_exec()
sub error_handler_mine()
Monitors a db size or summary statistics.
Copyright © 1998 by Edward Barlow
This work is a complete rewrite of code by Lars Kaarlson, who created a prototype that analyzed sp_sysmon output.
There are several sources of information that can be used to audit systems. This code attempts to do a rules based check of your servers and to identify problems that may be in existance on the servers. The rules are based on experience and sp_sysmon output is analyzed according to the (very loose) rules defined by Sybase in their system 11 documentation. They should, hopefully allow you to better understand your server performance without wading through tons of paper.
This analyzer is based on my experiences tuning a variety of servers. I intend to use it to assist me on contracts and as a part of the webmonitor package. You are welcome to use this code to analyze your servers, either as a part of webmonitor or as a stand alone package. If you notice any testable information that is missing from this analyzer, please contact the author and I will endeavor to put those rules into the system.
Successful analysis in many production environments is the only way this rules base can converge on a stable point, and ideas that you have can greatly help others. Please note that many of the thresholds are completely made up by me, and while they seem to work, i would appreciate feedback on how to come up with better numbers.
The extended stored procedure library must be installed in your servers for that part of the auditing to work. This can be accomplished by downloading the library from
http://www.tiac.net/users/sqltech
The following are the rules the parser uses to store data from sp_sysmon into variables. The actual subroutine name is s_process_msg_line.
The main body of rules is included as a fairly easy to read perl code file and in the audit stored procedures. Either may be modified. You should not need to modify other programs. Note the simple interface to the output of sp_sysmon, provided by Data() and get_lines(). A section (the first parameter is the highest level ("Kernel Utilization") and an item ("Engine Busy Utilization:") is indented by two spaces in showplan info. The third item of the Data function is the line info, the fourth the column id, and the last is a flag that determines if the code should produce an error if it is unable to find that data element (not all elements are always produced by sp_sysmon). The format should be simple enough for any decent programmer to understand and change.
sysmon_analysis.pl [-s$SYBASE] -SDSQUERY -UUSER -PPASSWORD [-ifile -hayc -ttime -ofile]
The SYBASE,DSQUERY,USER, and PASSWORD items are taken from variables by the
the same name if they are not passed directly into the program.
truncate all tables in a database. Does not handle ri, but you can probably run 2 times in a row to get through that. The user must have permission to truncate.
Build html based documentation from your perl application using the perl pod documentation format. The output of this library is a cross indexed frames based documentation set based on the multiple files in your perl application (ie. this works on many perl files instead of just 1).
The user will create a small document generator script (using Doc.pm) which will specify which files to extract pod from and in what order to do it (as well as other options). Whenever you wish to rebuild your documentation set you run the driver program and create them.
PARAMETERS: out_dir, source_dir, colorscheme, home_page, copyright_years, company_name, product_name, color_h1
Starts a frameset that can be referenced via the file index.htm in the directory you pass as out_dir. This file (index.htm) contains a master frameset, consisting of three parts: a page top (top.htm), a table of contents (toc.htm) on the left side, and a body on the right side (readme.htm). Filenames are fixed.
PARAMETERS: name, link
Places a special hyperlink in the table of contents. These links are special and will bring up a new browser window. This is for external links, perhaps to another set of documentation or to your company home page. 2 breaks appear this after link.
PARAMETERS: none
Write and close the files opened earlier.
PARAMETERS: files, ignore, dir, section
This is the main function. It documents a section (in the table of contents) from data in a single directory. By default it includes all files, but you can specify files with the 'files' parameter and can ignore files with 'ignore'. The title is 'section' and the directory used is 'dir'.
Source of Folder-Tree input files. These files are required for javascript. The source dir is an optional parameter. You may think the documentation looks better if this parameter is not passed :)
Output Directory For the web pages. It actually places stuff in the html subdirectory of this directory.
Your company Name.
Your Home Page for links.
Copyright Notice Years
Default to 1. If this is set to 2 then head2's show up in the documentation table of contents and head1's are not indented. If it is set to 1 then head1's in pod files are indented in table of contents (no head2's). If 0 then all head1's show up non indented (no head2's).
Product Name
Section header - goes in table of contents
All first level headings get colored - not just NAME blocks.
Change the color scheme of the documentation. Currently available color schemes are: DARKRED, PINK, OLIVE, LTBLUE, RED, ORANGE, LTGREEN, DKBLUE, BLUERED, and PURPLE.
The documentation shows in a nice 3 pane window. The top pane (top.htm) will give title info. The left pane (toc.htm) gives table of contents and navigation information. The right pane (readme.htm) will give documentation with anchors embedded so the table of contents can work.
Documentation can be built from directories by using the doc_dir function to read all files for pod documentation. Files can either be in perl source code (.pl) files or .pod files. Documentation can also come from files with .htm or .html extensions. These files are parsed so as to remove everything but the body section.
If reading pod files, the first file is readme.pod. After that it is alphabetical.
In addition to regular pod documentation, get any documentation between "^# ===" blocks (comments only). If line is of format ^#\s*(\w+)\s*: then word is a heading
Specifically the doc subdirectory will contain README.txt and individual doc pages and the html directory will contain x.html files (from x.pl), and an index.html which is composed of a readme.html and toc.html (table of contents)
The application will keep track of any keyword titles (ie items in the table of contents). When it is almost done, it will rewrite the main page and replace any words that match the title with self hyperlinks.
Generic log file filtering program. Can do lots of neat stuff. It is probably not the best way to do it, but it works. Features an optional patern file which can be used to keep track of "recent" changes to the file.
The patern file works this way. The first time through (no file will exist), the file will be writen with a byte offset and a the last line successfully processed. The next time through, this file can be read and if the appropriate line exists at the byte offset (that is a good way to double check that the log file has not been overwritten/removed or whatever), it is assumed that the file has simply been appended to. The search then starts at the byte offset from the patern file. If the string is not at that byte count, the patern file is ignored.
You may specify an filter file which contains directives on what lines to ignore. Additionally you may ignore lines by using the log_rm_pat function.
Set patern to remove from output lines. The lines are not removed, just the patern. The remove paterns should be a pipe (|) separated list. They may contain perl wild cards.
Set information that the output must contain. This can be either based on dates (using today/yesterday with date_fmt) or explicitly. The Search Patern Instructions are a pipe separated list of strings (format a|b|c). Date Format is parsed as follows:
Input Arguments
Sets debug flag. Only run if you want debug messages shown.
Input Arguments
Read filter instructions from filename
Input Arguments
Filter a File. Returns (num_rows_printed,num_rows_read,found_patern,returned_patern,byte_cnt,@output)
Input Arguments
Read a patern file. Returns byte count and start patern.
Input Arguments
Write out a patern file.
This is my cut at a generic subroutine library for sybase perl applications. I have used it for a variety of packages and it seems quite adequate. It currently uses dblib. These routines are useful if you only need a single connection to sybase.
Release Manager - By name perl interface to load database ddl. The library manages tables by archiving table data and then copying it back. Output is in a decently pretty format.
Requires Sybase::SybFunc library and Sybase::DBlib.
Files are installed in the order they are passed in - so another program will need to manage file installation order.
Common messages are filtered and ignored - these include sysdepends etc...
do_release( ARG=>VALUE, ARG=>VALUE... );
Webify.pm is a free perl library designed to simplify prototyping and building interactive cgi web sites.
The library will simplify design of a system with multiple pages that have a common look and and feel and is optimized for screens that call external programs/scripts to perform their business logic. The systems currently using Webify perform operations like data transfers and backups. Webify.pm provides a nice front end to upgrade menu scripts currently in use by operators, for submitting batch reports, and to allow users to monitor complicated nightly batch processing.
Webify.pm requires the sybase database. A future version will use either DBI or an ASCII file database for its internal operation.
Webify.pm is a library for building interactive cgi web sites. The library is designed for trivial prototyping and implementation of a dynamic web site. It is specifically designed for low volume sites (under 10000 hits per day) because the current implementation makes no attempt to optimize performance through the use of mod perl or fast cgi (although nothing precludes this). Performance is, however, fine for normal volume sites. Webify.pm adds many features to top of perl's built in CGI.pm. These features include:
Webify.pm dynamically builds an application of multiple connected pages using small perl scripts and a configuration file. A common navigation toolbar is placed at the top of the page and the system has all the features you would expect. For each screen, the developer is reponsible for coding 4 subroutines. 3 of these screens are usually empty. The developer is also responsible for maintaining two configuration files. BUTTON_COMMANDS.env contains lines mappings buttons (created with submit_button()) to external processing. GUI_CONFIG.env contains variables that define the look and feel for your application (toolbar, product name etc...).
The most important of the 4 routines is paint_form(), which draws the page. paint_form() will use CGI.pm routines to paint your form using print statements. The other routines are usually empty. form_initialize() allows any SPECIFIC preprocessing you might care about and is run after basic initialization but before the form does anything else. button_action_override() is used to override the data in BUTTON_COMMANDS.env, when the processing is done in the form or when the external command is complicated to set up. button_action_override() either sets a flag to indicate that it has handled the command or it returns a string which is the external program to run. If the defaults are to be used, the function returns the empty string "", and the BUTTON_COMMANDS.env file be read to map the button pushed to the external code processing. The final subroutine is validate_button_action() which returns a 1 if your form is ok, and a 0 (as well as populating the @err error array) if the form data has problems. Everything else is handled internally.
The general program structure therefore is:
All configuration options are stored in the GUI_CONFIG.env file, which defines the common look and feel for the application (company logo, server name...). Externally mapped commands (commands run when a button is pushed) are stored in BUTTON_COMMANDS.env.
Webify.pm works under UNIX or NT. It requires the Sybase Database, a commercial database to store security information. It also requires a Web Server like the freely available Microsoft IIS for NT or Apache (for NT or UNIX). It also requires perl.
The following files are shiped with webify:
the webify library. should be placed in @INC
the sybase library. should be placed in @INC
environment variables
external command processing
convert strings to gifs. Needed for page buttons.
A simple screen
A bunch of configuration menu stuff. Config1.exe is a PC executable, that simply echoes config1.html. Ignore it and use config1.html on UNIX.
Since webify.pm is new software, it is recommended that you contact the author at sqltech@tiac.net to have him help you install webify the first time.
Each system requires configuration, to define navigation buttons, company name and other cosmetic information, to map buttons to external commands, and to create a Sybase database repository for system security information. Then each screen requires one data file that uses Webify.pm and codes 4 functions.
To make a working system, please contact the author at sqltech@tiac.net, because he is too tired right now to finish this part of the documentation :).
A sample form coded in Webify looks like the following:
This results in a form with a nice background with your common navigation buttons across the top. The form will have a button labeled "PUSH ME" and will print a message when you push the button.
When you first use the form, you are required to log in. Your login expires if not used in a configurable period of time (3 hours default) and you will be prompted to log in again after this timeout. When you click on any of the submit buttons on your form (drawn using submit_button()), external processing will be called based on a configuration file or on the button_action_override() routine.
Forms can be debugged either by setting the perl $debug global variable to 1, or by setting the Shell Environment variable DEBUG to some value (and exporting DEBUG on UNIX). If either of these cases are true, well defined error messages will appear on the screen explaining everything that is happening as your form is handled.
Webify.pm has been extensively tested under Windows NT (apache and IIS) and UNIX (apache).
Each of the perl cgi scripts that generate forms must have the following four functions.
Called when a submit button (submit_button()) has been pushed and you do not want the default processing mechanism to be used. The default processing mechanism is described in the section on BUTTON_COMMANDS.env.
This function, if it returns a string, tells the forms system to run that string. Return an empty string if you wish to run default processing. You may also set $CONFIG{"query_results"}, which tells the system not to run any default processing AND tells the system that the command has been allready executed. Query results are the results. You are responsible in your paint function for showing these results to the user.
Check the fields on the form. Returns 1 if things are ok. It should populate @err and return 0 if not ok. @err is automatically displayed on the form if it is defined.
Prints out the form. Use CGI.pm to build the form.
Run at startup to initialize form. No return status or code. This function MUST not print anything, but can send messages to the system using the @err array. If @err is set, no commands can be run. The special routine form_uses_repository() sets the default database connection to the repository server instead of to the default server.
The following variables are exported from Webify.pm:
Form configuration information is stored in the %CONFIG hash. The form configuration information for a key can be accessed via $CONFIG{'key'}. The following are legitimate key values:
Title of the form
Is there a html help file. If not leave it undefined
Leave undefined - if defined the form is run in debug mode
If defined - external program output is html. Otherwise it is plain ascii output. This difference is that ascii output is printed with break tags, so if the program prints formatted output (with html tags), this variable must be set for the output to appear correctly. Obviously if your program can format html, you should set this.
You can specify the host to run on in button_action_override(). It can also be specified in the BUTTON_COMMANDS.env.
You can specify which user to run as in button_action_override(). This is unimplemented.
If your query is handled in button_action_override(), set this to the results. This then makes the engine not attempt to find a program to run the command out of BUTTON_COMMANDS.env - i.e. it acts just as if the button_action_override() returned a spaced string.
Webify currently requires Sybase::SybFunc, the sybase function library that is built on Sybase::DBlib. This means that forms have access to a connection by default. Queries can be run automatically using this connection. See the library for more information.
The Following internal functions are exported from form:
Used to expand a variable.
called in the form_initialize() routine if you wish the form to use the repository instead of the currently active target server & database.
Gets current server information from the repository. returns a list of:
Colors a font.
The main routine. Required in each form.
Used to color a font
print a dividing line
Increase font size for string
Decrease font size for string
increase font size AND make bold
Print a Submit Button. The button will be greyed out (pink) if auth_buttons{text} = 'N'
print a row of headings with each arg in separate cell
print a row with each arg in separate cell
run a system() command and return array with the results
Return list of all files in a directory
creates a hyperlink button
get database names appropriately. uses a cache so you dont need to refetch.
return @server_list
Initialize the system. The following steps are taken to initialize:
Sets DSQUERY/SQL_SVR_LOGIN/SQL_SVR_PASSWD/TITLE/BACKUP_DIR/ERROR LOG... Reset CONFIG variables dependent on these items
Print standard web page header. Arguments: optional $refresh_rate (in seconds). Includes anything necessary to make web page work (content type, and header). Puts a page title and company logo on that page.
print standard page footer including compyright...
See if you have logged in in the past CONFIG{LOGIN_TIMEOUT} minutes by ip and login.
draw a form that asks for login and password
Given a string - return string with variables replaced. First check environment from config files, then $ENV, then param()
given a string (button) -
If the file NEEDS_UPGRADE exists in the cgi directory, or if the GUI_CONFIG.env file does not exist, the system moves into the self configuration process.SEE ALSO
allsrv.pl
loop through your servers utility
DESCRIPTION
adduser -SSYBASE -Usa -PXXX -x -upaulr
AllSrv adduser -x -upaulr
USAGE
Usage: ./AllSrv Parameters
"./AllSrv MY_PROG OTHER_PARM"
"MY_PROG -SSRV -PPASS -UUSR OTHER_PARM"
audit_fetch.pl
analyze your sysaudits information
DESCRIPTION
USAGE
./audit_fetch.pl [-d] -USA_USER -SSERVER -PSA_PASS
optional parms
-t optional table name (default=sysaudits)
-h htmlize output
-p parse/summarize the sql by removing quotes etc
-DDB database name to use in selectivity criterion
-lLOGINNAME login name to use in selectivity criterion
-sSTARTTIME start time to use in selectivity criterion
-eENDTIME end time to use in selectivity criterion
-d DEBUG MODE
sort options (default is to sort text)
-o n num hits, b besttime, w worsttime, a avg time
NOTES
backup_db.pl
utility to backup databases
DESCRIPTION
bcp_in.pl
utility to copy files into a server
DESCRIPTION
USAGE
bcp_in.pl [-d] -UUSER -SSERVER -PPASS -DDB [-iDIR] File File File
bcp_out.pl
copy files out of a database
DESCRIPTION
USAGE
DB_LIST may include wildcards
If no TBLs defined, will bcp out all tables in the directory
OUTDIR is created if it does not exist
-d debug mode
check_app_cksum.pl
Check an application checksum (requires cksum command)
DESCRIPTION
EXAMPLE
# create my_file
check_app_cksum.pl -D/a/b/c -omy_file
# compare using my_file
check_app_cksum.pl -D/d/b/c -imy_file
USAGE
Checks application checksums against a file.
Use -f to show files not in both file and directory
Note that the prefix is removed from the file names to ease comparison. If
no prefix is sent, it is assumed to be same as Directory.
clean_ctrl_char.pl
cleanup control characters from files
USAGE
DESCRIPTION
clean_ctrl_M.pl
parse off ctrl
DESCRIPTION
USAGE
cat file | clean_ctrl_M
clean_ctrl_M -FFile
clean_ctrl_M -DDirectory
NOTES
config_report.pl
Configuration report
DESCRIPTION
WHAT IT PRINTS
SERVER: srvname
@@version
helpdb
configure
helpmirror
vdevno
helpdevice
helplogin
helpuser by db
all the reverse engineering routines
copy_db.pl
copy_db utility
USAGE
DESCRIPTION
REQUIRES
create_dflt_groups.pl
create read_only_group and super_user_group in your server
DESCRIPTION
USAGE
create_dflt_groups.pl [-dx] -USA_USER -SSERVER -PSA_PASS -DDB
-d debug mode
-x remove stuff only
create_ins_script.pl
create insert script utility
DESCRIPTION
USAGE
USAGE: create_ins_script.pl -Ssrv -Uusr -Ppass -Ddb -ttable -qquery
[-eexclcol1,exclcol2] -iintcol1,intcol2
-i no quote columns (used for user defined types)
-e exclude from report
Output is placed in tablename.ins
If no query passed with -q it will do a select *
crosstab.pl
create a crosstab output
DESCRIPTION
public group1 group2 group3
master 3 users 2 users 2 users 2 users
model 0 users 2 users 2 users 2 users
tempdb 0 users 2 users 2 users 2 users
sybsecurity 0 users 2 users 2 users 2 users
select name, count(*), "users"
from sysusers
etc etc....
public 4 users
group1 3 users
crosstab.pl -Uxxx -Sxxx -Pxxx -D% -Q$QUERY -R0 -C1 -O2,3
USAGE
crosstab.pl [-dx] -USA_USER -SSERVER -PSA_PASS -D[DB,DB2,DB3]
-Q[Q1,Q2] -R[Row1,Row2...] -C[Col1,Col2] -O[Out1,Out2] -ih
Column number 0 is the database name you are running in
EXAMPLES
custom_rpt.pl
run precanned sybase report
USAGE
-h html output, -x debug mode
Custom reports
1) Object Protections by Group (SIUDE)
2) Groups By Database
3) Users By Database
4) Num DBO Objects
5) Num Non DBO Objects
6) Login Report
7) Aliases By Database
NOTES
datacompare.pl
database data comparison
DESCRIPTION
USAGE
datacompare.pl [-xh] -Ssrv [ -Uusr -Ppass -Ddb ] -ssrv [ -uusr -ppass -ddb ]
Shows tables with different number of rows between two servers
B<-h> htmlize output
B<-c> show row counts instead of just summary info
B<-x> debug mode
B<-x> debug mode
NOTES
dbcc_db.pl
utility to dbcc databases
DESCRIPTION
dbcompare.pl
database comparison utility
DESCRIPTION
USAGE
Compare 2 Databases DDL. Can be from File (use -F) or DB (-SUPD)
If both -SUPD and -F are supplied, file is written.
-g no group permissions
-o object name
-h htmlize output
-i ignore identical output
-x debug mode
NOTES
debug_one_server.pl
utility to debug servers
DESCRIPTION
USAGE
debug_one_server.pl [-dh] -UUSER -PPASSWORD -SSERVER
DETAILS
* Checks stored procedure library
* run sp__auditsecurity
* check wierd select_into/trunc option definition on databases
* checks sysprocesses for blocked, log suspend, infected, sleeping io,
or stopped status
* foreach database
-> space used (data&log) from sp__qspace
-> sp__auditdb
-> database options
database options
configuration (default memory, allow updates, connections, updates)
-> not implemented
delete_db.pl
delete all data in database
DESCRIPTION
USAGE
delete_db server dbname login password
NOTES
depends_analyze.pl
sybase dependency analyzer
AUTHOR
V1.0 1997-09-13 By Lars Karlsson bergkarl@algonet.se
V1.1 1997-12-1 Ed Barlow did a major modification/cleanup job
- build_db No longer Perl4 compatible, but more standard
- Using Getopts for arguments
- Making it a Package (gzip distribution)
V1.2 1998-01-01 After getting the code from Edward
- I did some rewriting, bug fixes
V1.3 1999-02-10
- Complete Rewrite By EMB
FILES
SYNTAX
DESCRIPTION
DETAILS
FEATURES
BUGS
depends_get.pl
parse dependencies for an object and print output
SEE
do_release.pl
release management utility
DESCRIPTION
USAGE
NOTES
document_db.pl
database documentation utility
DESCRIPTION
USAGE
document_db.pl -dcodedir -SSRVR -UUSR -PPASS -DDATABASE [-x]
-x debug mode
Document a database based on source code and ddl
NOTES
drop_all_objects.pl
drop all objects in the database
DESCRIPTION
USAGE
USAGE: ./drop_all_objects.pl -UUSER -SSERVER -PPASS -DDB
NOTES
extract_ddl.pl
extract_ddl utility
DESCRIPTION
USAGE
extract_ddl.pl [-dx] -UUSER -SSERVER -PSA_PASS -DDB -Oobject
Extracts object ddl. Fully qualify object to get a users stuff.
You may ignore -U and -P options if you have set up password file
fix_db.pl
fix/set database dboptions
DESCRIPTION
USAGE
./fix_db -USA_USER -SSERVER -PSA_PASS
full_analysis.pl
Analyze a whole database using showplans
DESCRIPTION
INSTALLATION
USAGE
./full_analysis [-ds -hdir] -UUSER -SSERVER -PPASS -Ddb
-d debug mode
-h document root of optional web server
-s summarize only (dont run procs)
-v verbose ( more verbose than normal less than -d )
get_comments.pl
utility to extract comments from file
DESCRIPTION
get_interfaces.pl
print interfaces file utility
DESCRIPTION
USAGE
SAMPLE OUTPUT
BARLONTSERVER barlontserver 5000
BARLONTSERVER_BS barlontserver 5001
BARLONTSERVER_HS barlontserver 5003
BARLONTSERVER_MS barlontserver 5002
BARLONTSERVER_XP barlontserver 5004
log_file_filter.pl
Generic Error Log Filter
DESCRIPTION
USAGE
EXAMPLES
FILTER FILES
aix.excl - aix system log
backupsrvr.excl - sybase backup server messages
hpux.excl - hpux system log
linux.excl - linux system log
solaris.excl - solaris system log
sunos.excl - sunos system log
sybase10.excl - sybase log
DETAILS
EXCLUDE=pat - excludes lines with pat in them
ML_START=pat - excludes lines until patern specified by ML_END (next)
is found. Will print ML_END line.
ML_END=pat - stop ML_START block
EXCLNEXT=pat - excludes line with patern AND next line
EXCLSTART=pat - exclude only if patern starts line
BUGS
OTHER
nt_services.pl
service manager for NT
USAGE
DESCRIPTION
USAGE
nt_service.pl
nt_service.pl START service_name
nt_service.pl STOP service_name
nt_service service_name
nt_service running|stopped
nt_shutdown.pl
shutdown NT system
DESCRIPTION
passwd_rpt.pl
multi server report sa/sso/oper roles utility
DESCRIPTION
USAGE
./passwd_rpt [-ousxh]
-o oper roles only
-s sa roles only
-x sso roles only
-h htmlize output
-u print by user not server
EXAMPLE
> PRINT A LIST OF SA USERS BY USER
$ passwd_rpt -us
query_compare.pl
Compare Query Results in Two DB's
DESCRIPTION
USAGE
SAMPLE RUNS
#!/bin/sh
TBLS="tbl1 tbl2 tbl3"
SRV1=SYBASE_INT
SRV2=SYBASE_QA
for tbl in $TBLS
do
query_compare -Usa -S$SRV1 -PPASSWORD -Dxrm_db -usa \
-s$SRV2 -pPASSWORD -dxrm_db -Q"select * from $tbl"
mv data/outS data/$tbl.$SRV1
mv data/outs data/$tbl.$SRV2
done
rebuild_index.pl
Rebuild Indexes
DESCRIPTION
USAGE
rebuild_index.pl -UUSER -PPASS -SSERVER -DDB [-oOUTFILE|-iINFILE] -c
-c : correct duplicate keys in indexes. Deletes duplicate data. One random
row in unique indexes will be kept.
-r : report only - this will report indexes needing rebuild
as well as duplicate data but will not rebuild any indexes.
-d : performs drop operations as well as build operations
reperl.pl
DESCRIPTION
USAGE
reperl.pl file file file...
reperl.pl *
NOTES
: # use perl
eval 'exec perl -S $0 "$@"'
if $running_under_some_shell;
rev_db.pl
Reverse engineer sql to individual files
DESCRIPTION
USAGE
-E - no permissions
if no objects passed, then it will do all objects except built
in constraints that are found in the database.
DB_LIST may include wildcards - but you probably dont want this as
there is only one output directory and the files will overwrite
each other.
directory passed with -O must exist
setup.pl
install patch to an application as appropriate
DESCRIPTION
USAGE
IMPLEMENTATION DETAILS
CUSTOMIZATION
REQUIREMENTS
TO DO
the gui
On a per server/db/object/version basis.
showplan_analyze.pl
showplan analyzer
DESCRIPTION
USAGE
FUNCTIONS
THE FOLLOWING SUBROUTINES HAVE BEEN DEFINED AT THIS POINT:
monitor.pl
monitor database (size or performance)
USAGE
monitor.pl -USA_USER -SSERVER -PSA_PASS -DDB_LIST -ccnt -ld -tsec
-c count - times to repeat - if no count then loops forever
-t sleep time in seconds between run
-l show log space (defaults to -ld)
-d show data space
-s space (data and log)
-q quick statistics
DESCRIPTION
SAMPLE OUTPUT
$monitor.pl -d -USYS_INSTALL -PPASSWORD -SSYBASE_LOCAL -Dxrm%db -c3
Database Allocation: xrm_db Size=20480 (40MB) Log=5120 (10MB)
Database Allocation: xrm_security_db Size=10240 (20MB) Log=2560 (5MB)
Database Allocation: xrm_sod01_db Size=20480 (40MB) Log=5120 (10MB)
----------------------|----------------------|----------------------|
xrm_db| xrm_security_db| xrm_sod01_db|
----------------------|----------------------|----------------------|
Db: 35.8MB|Db: 5.2MB|Db: 28.9MB|
Db: 35.8MB|Db: 5.2MB|Db: 28.9MB|
Db: 35.8MB|Db: 5.2MB|Db: 28.9MB|
sysmon_analysis.pl
SP_SYSMON SYSTEM ANALYZER
AUTHOR
SUMMARY
INSTALLATION
PARSER RULES
*) ignore blank lines or lines with -----------
*) a line of === is a new section. The first two sections are headings.
and are treated separately.
*) the first non blank line after --- is section heading (section_line==1)
*) Indented 1 or 2 Characters and we are an item (within a section)
parse down to first field (or set of words - the key)
- set item_name
- set section_count{sctn} = ++
- item_count{sctn:itemnm}= 1
- item_data{sctn:itemnm}= $msg\n
*) otherwise must have an item
$section_count{$section_name}++;
$item_count{$section_name.":".$item_name}++;
$item_data{$section_name.":".$item_name} .= $msg."\n";
THE RULES
USAGE
-d : debug mode
-i file : use file name as sysmon input instead of running directly (sets -y)
-x : run sp_sysmon
-y : analyze sp_symon
-a : run audit analyzer
-c : run sysconfigure monitor
-r : print raw sysmon output too (defined automatically if -o used)
-h : html'ize output for a web browser to view
-o file : output file for raw sysmon output
-q : quiet mode - print little to screen
-t time : time is the time interval for sp_sysmon (default = 10 minutes)
trunctate_db.pl
truncate database utility
DESCRIPTION
USAGE
usage: truncate_db server dbname login password
Doc Module
build html frames based documentation
SYNOPSIS
DESCRIPTION
FUNCTIONS
PARAMETERS DESCRIPTIONS
NOTES
CROSSREFERENCE
EXAMPLE
use strict;
use Doc;
doc_init(
out_dir => '../web' ,
home_page => 'http://www.tiac.net/users/sqltech',
copyright_years => '1998-1999' ,
company_name => 'IQ Financial' ,
product_name => 'OPERATIONS MGR'
);
# add link to top level
doc_navlink( name=>"Return To Top Level", link=>"../index.htm");
#any pod documents in ../web/pod are in DOCUMENTATION section
doc_dir(
dir => "../web/pod" ,
section => "DOCUMENTATION"
);
# SCREENS section
doc_dir(
ignore => ['func.pl','web_run_cmd.pl','web_view_file.pl','web_waitfor.pl'],
dir => "../web" ,
section => "SCREENS"
);
# INTERNALS section
doc_dir(
files => ['func.pl','web_run_cmd.pl','web_view_file.pl','web_waitfor.pl'],
dir => "../web" ,
section => "INTERNALS"
);
doc_finish();
LogFunc Module
Parse Through Log Files
DESCRIPTION
log_filter_file - read a filter directives file
log_process_file - handle an input file and return filtered results
log_rd_patern_file - read patern file
log_wr_patern_file - write patern file
log_debug - set debug mode
log_grep - set paterns that must be in output string
log_rm_pat - this patern should not appear in output
FUNCTIONS
mm 1-12 month
yy 99,00,01 year
dm 1,9,10,13 day of month
dd 01,09,10,13 day of month
mon Jan Feb... month
search_patern - grep paterns in addition to date filters
today - if defined do today
yesterday - if defined do yesterday
date_fmt- search string format for dates
file - patern file to write
file - input file to read
ignore_blanks - ignore blanks if defined
search_patern - search patern of last line read
byte_offset - byte offset to start search of search_patern from
max_lines - max output lines
Output Parameters
$num_rows_read - rows read
$num_rows_returned - rows filtered
$found_input_patern - did you find the input patern
$lastline_read - can be used as patern for next search
$byte_count - byte count of this last line
@output - filtered output
file - patern file to write
patern - line on the file
bytes - byte count of last read line
file - patern file to write
Sybase::SybFunc Module
Perl Sybase Subroutines
DESCRIPTION
AUTHOR
Edward Barlow
mail: sqltech@tiac.net
url : http://www.edbarlow.com
All Rights Reserved
FUNCTION OVERVIEW
db_add_line_number
db_bcp_table
db_colorize_sql - format sql text
db_eachobject
db_get_sql_text - get sql text for an object
db_get_dirlist
db_has_login_failed
db_is_passwd_expired
db_msg_exclude - tell message handler to ignore this message
db_msg_ok - tell message handler to error out on this message
db_set_ignore
db_close_sybase - close you connection
db_syb_ping - ping your server
db_connect - connect to your server
db_use_database - use database
db_query_to_hash - run_query_to_hash
db_query - the main query
db_decode_row - interpret output from db_query()
db_query_to_table - command - output html formatted
db_set_debug - set debug option
db_set_web_page - set html_on option
db_set_password_expire_check - setup password expiration checking
db_get_errorlogs - return a list of error log files
db_set_mode - set run mode
LIBRARY FUNCTIONS
DESCRIPTION: cleans up and exits the server
RETURNS: no return code
DESCRIPTION: Ping server
RETURNS: 0 success or $msg string
DESCRIPTION: connects to sybase using login & password.
RETURNS: 1/0
DESCRIPTION: use a database
PARAMETERS: $db - database name
RETURNS: returns 1 (success) or 0 (fail)
DESCRIPTION: run a command
PARAMETERS:
$db - database name
$query - what to run
$die_on_err (optional) - if defined then croak if an error occurrs
$print_hdr - if defined first row will be header info
$add_batchid - batch id should be added
$srv - OPTIONAL SERVER ONLY FOR THIS COMMAND
$login - OPTIONAL LOGIN TO SERVER ONLY FOR THIS COMMAND
$pass - OPTIONAL PASSWORD TO SERVER ONLY FOR THIS COMMAND
RETURNS: returns array (success) or undef (fail). array must be decoded
DESCRIPTION: decodes packed row returned by the functions
PARAMETERS: $row - packed row
RETURNS: array - each element is a cell for row
DESCRIPTION: Run sybase query to table (or multiple tables)
RETURNS: table formatted output
DESCRIPTION: adds to list of messages to exclude
PARAMETERS: message number
RETURNS: N.A.
NOTES: Currently list of messages is
5701,3,4 - use database successful message
4002 - login incorrect message
911,13,16,18,19 - Unable To Use Db
20012 - srv name not in interfaces file
20018 - general message
20019 - results pending
DESCRIPTION: remove message from list of messages to exclude
PARAMETERS: message number
RETURNS: N.A.
DESCRIPTION: return text for an object
NOTES: handles any object type in current db
PARAMETERS:
$objname: object name in current db
$print_go: do you wish to include a line with the word go
DESCRIPTION: formats sql text
DESCRIPTION: Returns list of files in directory
DESCRIPTION: returns a list of sybase errorlogs.
PARAMETERS: errorlog_type:
if type=0 SYBASE
if type=1 Backupserver
if type=2 Both
DESCRIPTION: runs cmd on each object of type and print output
PARAMETERS:
$do_print: print text of what you are doing too
$type: object type
DESCRIPTION: sets the mode to NORMAL or INLINE.
RETURNS: Returns current mode (before set).
DESCRIPTION: Returns 1 if Password Expired. Else 0.
Valid after login attempt & db_set_password_expire_check().
DESCRIPTION: Sets Debug Flag
DESCRIPTION: Sets NEWLINE STRING & controls printing of pre on errors
DESCRIPTION: Allows Pasword Expiration Checks
Sybase::Release Module
release management library for sybase
DESCRIPTION
USAGE
objects - space separated list of file names to install (in order)
batch_die - its a batch (does not ask for confirmation) and you want to
die cleanly if there are any errors
batch_nodie - its a batch (does not ask for confirmation) and you want to
continue if there are any errors
username - server login
servername - server DSQUERY
password - server password
dbname - database to install into. May include sybase wild cards
ctlfile - optional control file
dir - default directory objects will be found in
start_at_fileno - optional object # to start install at (for restartablility)
end_at_fileno - optional object # to end install at (for restartablility)
noexec - dont actually run anything
norules - ignore rules and defaults (.rul and .def) files
nosave - dont save table data
procs_only - only stored procedures
new_obj_only - only objects that dont exist
EXAMPLE CODE
use Sybase::Release;
do_release( batch_die => $opt_x,
username => $opt_U,
servername => $opt_S,
password => $opt_P,
dbname => $opt_D,
ctlfile => $opt_c,
dir => $opt_V,
start_at_fileno => $opt_s,
end_at_fileno => $opt_e,
batch_nodie => $opt_b,
noexec => $opt_n,
norules => $opt_r,
nosave => $opt_s,
procs_only => $opt_T,
new_obj_only => $opt_N,
objects => join(" ",@ARGV),
);
Webify Module
Dynamic Web Site Authoring System Library
SYNOPSIS
DESCRIPTION
common look and feel
easy coding
trivial external command execution
full featured security
built in self documentation
easy to add new pages
form_initialize()
if param("button") # button was pushed
$rc = validate_button_action()
$cmd = button_action_override() if $rc==1;
handle($cmd) unless $#err>=0; # if no command read from file
paint_form()
REQUIREMENTS
WHAT COMES WITH WEBIFY.pm
CONFIGURATION
SAMPLE FORM
use CGI qw(-no_debug :standard);
use Webify;
use vars qw($debug %CONFIG @err );
$CONFIG{"title"}="Welcome";
sub button_action_override {
$CONFIG{'query_results'}='You Pushed The Button';
return "";
}
sub validate_button_action { return 1; }
sub paint_form {
print
$CONFIG{'query_results'},
"<CENTER>",
h2($CONFIG{"PRODUCT_NAME"}." ".$CONFIG{"VERSION"}),
"by ", "<A HREF=",$CONFIG{"HOME_PAGE_URL"},">",$CONFIG{"OWNER"}."</A>",
br,
submit_button("PUSH ME!"),
"</CENTER>";
}
sub form_initialize { }
new_form();
DEBUGGING
PLATFORMS
USER DEFINED FORM FUNCTIONS
$CONFIG{"query_results"} Return String Action Empty String Empty String Default Processing String Empty String No Processing - Command has been run Empty String String Run Return String as a command VARIABLES
%CONFIG @err $debug %RAWCONFIG
WEBIFY FUNCTIONS
(application_version, sybase_version, monitor_by_hostname, login,
password, sybase_error_log, bs_error_log, bs_server_name , base_backup_dir,
server_type, hostname, port)
INTERNAL FUNCTIONS
Read GUI_CONFIG.env into %RAWCONFIG and %CONFIG
Log in to the repository
login_to_system()
exec web_init_user_proc login ip
Make sure user is in web_user_info,web_permissions,web_server_user_map
exec $CONFIG{MENU_GET_PROC}
(select legal screens and buttons)
if( button pushed )
handle 'SET DEBUG MODE' with web_set_user_info_proc
handle 'TURN OFF DEBUG MODE' with web_set_user_info_proc
handle 'CONNECT TO SERVER' with web_set_user_info_proc
exec web_get_user_info_proc IP LOGIN into $cursvr,$debug
if CONFIG{DSQUERY}
$cursvr=CONFIG{DSQUERY}
else
exec web_get_server_list_proc => @server_list
exec web_get_server_info_proc $curserver => @server_info
exec web_update_login_time login,ip
reset_server $cursvr unless CONFIG{DSQUERY}
if $cursvr ne CONFIG{REPOSITORY_SERVER}
disconnect
log in to server
reset_server REPOSITORY_SERVER
form_initialize()
if ! login
select activelogin,minutes from web_user_info where ip=@ip
if minutes<CONFIG{LOGIN_TIMEOUT}
login=activelogin
return
paint_login_form minutes
else
login to repository
paint_login_form if fail
returns 1 for authorized and
0 for not authorized.
OTHER NOTES
This output is documentation for the SQL Technologies SYBASE UTILITIES.
copyright © 1998-2000 By
SQL Technologies