will execute sp_helptext on each proc in any databases that match the wildcard string xrm%db.
Output is tab separated and no headers are printed (its not isql you know)
SEE ALSO
AllSrv - AllSrv and AllDb can be used in conjunction easily
allsrv.pl
loop through your servers utility
|
DESCRIPTION
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 your password file as per Repository.
In other words... if you wish to run a program to remove user paulr on a server you might type
adduser -SSYBASE -Usa -PXXX -x -upaulr
but with this utility, you could run that command on ALL your servers in one fell swoop with:
AllSrv adduser -x -upaulr
As you can see, the -U,-P, and -S parameters are added automatically
USAGE
Usage: ./AllSrv Parameters
Generic program to run on all servers. If you type
"./AllSrv MY_PROG OTHER_PARM"
you will end up executing
"MY_PROG -SSRV -PPASS -UUSR OTHER_PARM"
for all the servers in your password.cfg file.
allsrv_query.pl
loop through your servers utility
|
DESCRIPTION
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 your password file as per Repository.
In other words... if you wish to run a program to remove user paulr on a server you might type
adduser -SSYBASE -Usa -PXXX -x -upaulr
but with this utility, you could run that command on ALL your servers in one fell swoop with:
AllSrv adduser -x -upaulr
As you can see, the -U,-P, and -S parameters are added automatically
USAGE
Usage: ./AllSrv Parameters
Generic program to run on all servers. If you type
"./AllSrv MY_PROG OTHER_PARM"
you will end up executing
"MY_PROG -SSRV -PPASS -UUSR OTHER_PARM"
for all the servers in your password file.
allsrv_query_long.pl
loop through your servers and run a query
|
Synopsis
$ perl allsrv_query_long.pl
Usage: allsqlsrv_query_long.pl --QUERY=Query --COLUMNS=1,3,5 --SHOWQUERIES --PRINT_HDR --TYPE=SQLSVR|SYBASE --DEBUG --HTML --HEADER=col1,col2 --SHOWSERVER
Generic program to run a query on all servers. If you type
"allsrv_query_long.pl Query"
you will end up executing
Query
on all your servers from the master database.
DESCRIPTION
This is the long arguments version of allsrv_query.pl (and should be used in preference). This program loops through all sybase and/or sql servers in your config files and runs a single query in them - formatting output appropriately. You can control the columns to show, whether its html output etc...
This is quite useful to get administrative values (like procedure cache size) from your servers or to audit them
audit_fetch.pl
analyze your sysaudits information
|
DESCRIPTION
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.
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
html output is the preferred method of viewing the logs as tables are perfect for this kind of thing.
bcp_out.pl
copy files out of a database
|
DESCRIPTION
Bcp all tables out of a database and into flat files The files can be read with bcp_in.pl.
USAGE
bcp_out.pl [-DEBUG] [-BCP_COMMAND=cmd] [-BCP_ARGS=args] [--NOEXEC] --SERVER=xxx --USER=xxx --PASSWORD=xxx --DATABASE=db -EXCLUDE=TBL,TBL -INDIR=INDIR Tbl Tbl Tbl
Extracts data from a database
-BCP_ARGS=args for bcp - default is -n
-EXCLUDE=exclude tables (separated by comma)
-DEBUG debug mode
bcp_in.pl
utility to copy files into a server
|
DESCRIPTION
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.
USAGE
bcp_in.pl [-d] -UUSER -SSERVER -PPASS -DDB [-iDIR] File File File
bcp_out.pl
copy files out of a database
|
DESCRIPTION
Bcp all tables out of a database and into flat files The files can be read with bcp_in.pl.
USAGE
bcp_out.pl [-DEBUG] [-BCP_COMMAND=cmd] [-BCP_ARGS=args] [--NOEXEC] --SERVER=xxx --USER=xxx --PASSWORD=xxx --DATABASE=db -EXCLUDE=TBL,TBL -OUTDIR=OUTDIR Tbl Tbl Tbl
Extracts data from a database
-BCP_ARGS=args for bcp - default is -n
-EXCLUDE=exclude tables (separated by comma)
-DEBUG debug mode
bcp_out.pl
copy files out of a database
|
DESCRIPTION
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.
USAGE
bcp_out.pl [-dx] -UUSER -SSERVER -PPASS -DDB_LIST -oOUTDIR Tbl Tbl Tbl
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
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:
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
Usage: check_app_cksum.pl -DDirectory [-oOUTFILE|-iINFILE] -RPrefix -f
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
clean_ctrl_char.pl FILE FILE FILE ...
DESCRIPTION
Clean up control characters from files.
clean_ctrl_M.pl
parse off ctrl
|
DESCRIPTION
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.
USAGE
cat file | clean_ctrl_M
clean_ctrl_M -FFile
clean_ctrl_M -DDirectory
NOTES
The program is simple.
brdcst_interfaces.pl
broadcast interfaces file to other servers
|
DESCRIPTION
UNDER HEAVY DEVELOPMENT
Broadcasts your interfaces file to other machines. Requires homgenious environment.
copy_db.pl
copy_db utility
|
USAGE
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.
DESCRIPTION
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.
REQUIRES
Extended stored procedure library Sybase::DBLib
copy_statistics.pl
Copy optdiag stats around
|
USAGE
copy_statistics.pl.pl -UUSER -SSERVER -PPASS -nmax_num_users
EXAMPLE
/usr/local/bin/perl-5.6.1 /apps/sybmon/dev/ADMIN_SCRIPTS/bin/copy_statistics.pl
-SERVER=SYB1 -USER=sa -PASSWORD=xxx -DATABASE=clientmlp --TOSERVER=SYB2
--TABLE=hts_exec_control,hts_ord_control,hts_exec_adj
Copies stats for the 3 three listed tables from SYB1 to SYB2
copy_syslogins.pl
maintain syslogins on servers that should be identical
|
DESCRIPTION
Some servers should have identical syslogins. This simple utility maintains a slave server based on a master server. The program will abort if the logins can not be maintained.
USAGE
copy_syslogins.pl -Fsrv [ -Uusr -Ppass ] -tsrv [ -uusr -ppass ]
Note the syntax -F (FROM) server with login/pass from (-U/-P)
and -t (to) server with login/pass from (-u/-p)
Copy New Logins Over (they must be mostly identical to start)
NOTES
Requires select into/bcp on tempdb in target server. Requires source and target server to be identical versions. This is what i use to maintain servers that should have exact copies of logins from produciton (eg. hot backups, systems used for split nightly batch runs, and some developement servers)
crdate_report.pl
simple report on objects created
|
DESCRIPTION
A basic security report. Only works on production servers.
USAGE
Usage: crdate_report.pl --SYSTEMS=system[,system]
create_dflt_groups.pl
create read_only_group and super_user_group in your server
|
DESCRIPTION
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.
USAGE
create_dflt_groups.pl [-dx] -USA_USER -SSERVER -PSA_PASS -DDB
-d debug mode
-x remove stuff only
note database can be a wild card
create_ins_script.pl
create insert script utility
|
DESCRIPTION
Output a set of insert statements that will rebuild the data in a server.
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
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:
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
You would first write a query that looks like
select name, count(*), "users"
from sysusers
etc etc....
To Return
public 4 users
group1 3 users
Of course this only works in one database. You then run your cross tab creator and it will create the crosstab.
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
-D - Database list (may have wildcards)
-Q - Query List
-R - Rows
-C - Columns
-O - Output
-i - integer output (sum em up)
-h - html output
Column number 0 is the database name you are running in - unless u dont pass a server/user/password and are working on all your servers as per the Repository module. In that case, column number 0 is the server and column number 1 is the database.
EXAMPLES
- USER REPORT FOR YOUR SERVER
crosstab.pl -D% -Qsp__helpuser -R1 -C0 -O4 -SSYBASE_QA3 -USYS_INSTALL -PPASSWORD
- OBJECT PROTECTIONS IN CERTAIN DATABASES
crosstab.pl -Dxrm_db,xrm_sod% -Qsp__objprotect -R1,2 -C3 -O4 -SSYBASE_PROD -USYS_INSTALL -PPASSWORD > xxx.csv
custom_rpt.pl
run precanned sybase report
|
USAGE
Usage: custom_rpt.pl -USA_USER -SSERVER -PSA_PASS -RREPORT_NUM
-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
Runs predefined crosstab reports. Requires extended stored procedure library.
cvt_table_to_view.pl
make a view on a table
|
DESCRIPTION
Simple utility to create byname views on tables
data_mine.pl
search data on tables
|
DESCRIPTION
parses where clause and builds data extract of table data which it searches. so... you pass whereclause. This gets parsed and tables with either all the appropriate keys are extracted UNION tables with one row after a subset of the keys is checked. This data is then searched for column info with the preset values
USAGE
./data_mine.pl [-dx] -USA_USER -SSERVER -PSA_PASS -DDB -ooutfile -iinfile
-Wwhereclause -Ssearchclause
-d debug mode
you can create output file by putting -o (and no -s)
you can search with -s and -i
BUGS
im going to rely on decent design - if you have int columns with the same layout as your char data - its gonna bomb (why do you have columns named the same thing with 2 different data types anyway).
well data mining is kind of brute force
data_mover.pl
move data around!
|
USAGE
data_mover.pl -Ssrv -Uusr -Ppass -Ddb -Qquery -ssrv -uusr -ppass -ddb -ttbl -Kkey1,key2
-O Outfile
Source may be -FFile or -U/-S/-P/-D and -Q Target is -u/-s/-p/-d Table is -t
-K is the keys for the rows -Ilines_to_ignore
-I1 would ignore the first line
-I4 would ignore lines 1 to 4
-TStartString -Cvals -Nnum_fields_min
-Rdelimeter
This defaults to a comma but you can pass whatever you want
-C -copy down first vals values if no value -istr1,str2 -> to ignore
SYNOPSIS
Either pass -U/-P/-S or a -Ffile Must pass -u/-p/-s of the target
-t TABLE is the table you want to copy into or to copy from one server to another
datacompare.pl
database data comparison
|
DESCRIPTION
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.
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
Requires extended stored procedure library
dbcompare.pl
database comparison utility
|
DESCRIPTION
Compares two databases. Checks the following things:
- object existence
- trigger names on tables
- Column definitions on table
- Object text if the object not a table
USAGE
dbcompare.pl [-xh] -FFile -Ssrv -Uusr -Ppass -Ddb -ffile -ssrv -uusr -ppass -ddb
Compare 2 Databases DDL. Can be from File (use -F) or DB (-SUPD)
If both -SUPD and -F are supplied, file is written.
If you ignore one server (ie the lower case side), the values are copied from the other. This saves typing
-g no group permissions
-o object name
-h htmlize output
-i ignore identical output
-x debug mode
EXAMPLE
I want to compare 2 databases (db1 and db1_bak) on one server:
perl dbcompare.pl -Usa -SSVR1 -Psrv1pass -Ddb1 -ddb1_bak -i
NOTES
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).
dbschema
a script to extract the schema from a Sybase ASE database.
|
Readme
dbschema.pl is a Perl script that will extract a database structure from a Sybase ASE database utilising the Sybase::DBlib module.
Description
Whilst schema extraction is what this is script is all about, it can do that one function in probably as flexible a manner as you could wish.
It can extract a single database into a single file, a single file per object type (tables, procs, indexes etc) or a single file per actual object. The number of options is getting so large that I am writing a Tk GUI to make it easier to manage, as well as the ability to extract single objects in a point and shoot fashion!
To install Sybase::DBlib, you need to install the Sybperl package that can be obtained from CPAN or directly from Michael Peppler's site:
http://www.mbay.net/~mpeppler
There is an FAQ and a mailing list for Sybperl, details of which can be found on Michael's site.
There is nothing to actually install in order to get dbschema.pl to work.
Home: The latest release of dbschema.pl can be obtained from
http://www.midsomer.org
Maintainer: David Owen (dowen@midsomer.org)
Anyone interested in contributing, please get in touch. If you find this script useful, then I would be grateful if you would send me details of your locality throughout the world so that I can add you to my xearth file.
Thanks
dowen
Prerequisites
This script reuires the Sybase::DBlib module.
Corequisites
None.
=pod OSNAMES
Any that support Sybase::DBlib.
=pod SCRIPT CATEGORIES
Databases/Sybase
debug_one_server.pl
utility to debug servers
|
DESCRIPTION
Check out your server! Should audit as much as it can!
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
Deletes all data in a database (using delete command).
USAGE
USAGE: delete_db.pl -UUSER -SSERVER -PPASS -DDB
Deletes all data in a database (using delete command).
-E TABLES (comma separated list of tables to EXCLUDE)
-I TABLES (comma separated list of tables to INCLUDE)
-n (noexec)
NOTES
might blow up on ri. If it does... just run it again :)
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
perl depends_analyze.pl -S server -U user -P password [-D database] [-H output directory ] [-I $SYBASE] [-q]
-q for quiet mode
-Tsybase|sqlsvr
DESCRIPTION
This script extracts relationships (dependencies) for a server. This can be used for impact analysis (show me the impact if i change an object). It also works to help understand all relationships between triggers, procedures, tables, views.
DETAILS
Output File Named is created in directory named depends.\$DSQUERY
FEATURES
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.
BUGS
Constraints Not Implemented.
update #tr set PX_LAST=isnull(b.PX_LAST,-1) from #tr a, riskdb..BLOOMBERG_SECMSTR b where a.ID_BB_UNIQUE = b.ID_BB_UNIQUE
Parses Wrongly
depends_get.pl
parse dependencies for an object and print output
|
SEE
See depends_analyze.pl
do_release.pl
release management utility
|
DESCRIPTION
Release Manager - Loads database based on ddl files.
This is a nice way to install your ddl while preserving table data. Behavior is dependent on file name (tables, for example, should have .tbl). Unknown extensions get treated as generic sql scripts. You may pass the objects in to the script or can list them (in order) in a control file (defined with -c).
The utility manages tables by archiving the data and then copying it back. This is a great tool to manage upgrades of databases.
USAGE
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
NOTES
If u do a use XXX statement as a batch, your db will be set to that database
document_db.pl
database documentation utility
|
DESCRIPTION
Self document your database! Uses source code and the stuff in the system tables.
USAGE
document_db.pl -dcodedir -SSRVR -UUSR -PPASS -DDATABASE [-x]
-x debug mode
Document a database based on source code and ddl
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
NOTES
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.pl
drop all objects in the database
|
DESCRIPTION
drop all objects from a database
USAGE
USAGE: ./drop_all_objects.pl -UUSER -SSERVER -PPASS -DDB
NOTES
does not handle failure well. RI issues?
dump_sybase_mon_tables.pl
dump sybase monitoring tables out to .csv files for investigation
|
USAGE
dump_sybase_mon_tables.pl -USER=USER -SERVER=SERVER -PASSWORD=PASS -OUTDIR=dir
DESCRIPTION
Creates about 10 files in .csv format that can be used to interogate server state in the event of an emergency. Basically it creates a set of .csv files that you can browse (with excel ) to see what was going on at the time of the “incident”. This includes very detailed process information – like showplans and the sql for all running processes. It creates a lot of output that needs to be pieced together – but when something weird happens and you have no time for diagnostics because you need to fix production, this is the program to run (quickly) – before you fix the problem. Takes about 10 seconds to run.
The program requires MDA tables to be set up and should be tested PRIOR to trying it in an emergency. Run it on a production server tho or you will have limited results.
dump_sybase_rep_server.pl
dump sybase rep server state for investigation
|
USAGE
dump_sybase_rep_server.pl -USER=USER -SERVER=SERVER -PASSWORD=PASS -OUTDIR=dir
DESCRIPTION
Creates dump of rep server info in .csv format that can be used to interogate server state in the event of an emergency. Basically it creates a set of .csv files that you can browse (with excel) to see what was going on at the time of the “incident”. This includes very detailed process information – like admin who,sqt and other basic rep server queries. It creates output that needs to be pieced together – but when something weird happens and you have no time for diagnostics because you need to fix production, this is the program to run (quickly) – before you fix the problem. Takes about 1 seconds to run.
extract_ddl.pl
extract_ddl utility
|
DESCRIPTION
Extract DDL for object and print it.
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
file_loader.pl
generic utility to load a file
|
DESCRIPTION
This program actually reads a fixed format file and runs a stored procedure on each row. The stored procedure can, if you want, insert into a table.
USAGE
USAGE: file_loader.pl --LOGFILE=file --REJECTFILE=rfile --PROC=proc -SERVER=srv -USER-usr -PASSWORD=pass -DATABASE=db -FILE=file -DEFFILE=def --SKIPTAIL=rows --SKIPHEAD=rows [-NOEXEC] [-DEBUG]
MANDATORY ARGUMENTS
SERVER : TARGET SERVER INFORMAITON
USER : TARGET SERVER INFORMAITON
PASSWORD : TARGET SERVER INFORMAITON
DATABASE : TARGET SERVER INFORMAITON
FILE : Input File
PROC : Stored procedure to call on each row of the file
DEFFILE : Definition File : $colname,$startcol,$lth,$type
type = c: character field
type = c-: character field, "-" gets removed
type = yyyymmdd: date field in the form yyyymmdd
type = yyyy-mm-dd: date field in the form yyyy-mm-dd
type = mmddyy: date field in the form mmddyy
type = nD where D is a number of 1 or more digits:
number field written with implied decimal point
before the last D-many digits
type = n: plain number field (possibly with decimal point)
blank field is mapped to 0
OPTIONAL ARGUMENTS
DEBUG : Call the program in diagnostic mode
DODELETE : run query before you load the
delete $DODELETE where FILENAME='".basename($FILE)."
SKIPHEAD : skip this number of rows from the head of the file
SKIPTAIL : skip this number of rows from the end of the file
REJECTFILE : File name for rows that dont load correctly
NODETAILS : Dont print the query that you run
NOEXEC : No Exec Mode - Just print the queries
=cu
fix_db.pl
fix up your databases
|
DESCRIPTION
Fixes up your databases. Right now it will perform the following operations which i believe should be non destructive.
If data and log on same device -> set truncate log on checkpoint
If Select Into/Bulk Copy set -> set truncate log on checkpoint
Turns off sp_configure allow updates if set
removes objects owned by users with no uid
Deletes sysusers where there is no suid ( and removes their objects )
I believe these rules are non destructive
USAGE
./fix_db -USA_USER -SSERVER -PSA_PASS
or
./fix_db -A
if you have your password file defined to go through all your servers
-x noexec
full_analysis.pl
Analyze a whole database using showplans
|
DESCRIPTION
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.
INSTALLATION
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.
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 )
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
get_comments.pl
utility to extract comments from file
|
DESCRIPTION
Extracts comments of the style /* */, --, and #
get_interfaces.pl
print interfaces file utility
|
DESCRIPTION
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.
USAGE
get_interfaces.pl [search_argument] [-i interfacesfile ]
-c - reformat column order or it will go 1 2 3
SAMPLE OUTPUT
BARLONTSERVER barlontserver 5000
BARLONTSERVER_BS barlontserver 5001
BARLONTSERVER_HS barlontserver 5003
BARLONTSERVER_MS barlontserver 5002
BARLONTSERVER_XP barlontserver 5004
USAGE
kill_pid.pl -USA_USER -SSERVER -PSA_PASS
log_file_filter.pl
Generic Error Log Filter
|
DESCRIPTION
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
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
EXAMPLES
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
FILTER FILES
Currently, the following files can be used for filter purposes:
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
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:
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
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.
BUGS
Converts all * characters to a - to deal with perl wildcarding. This should only be noticable on the output prints
monitor_a_table.pl
track a single tables size and incremental
|
USAGE
monitor_a_table.pl -TABLE=tbl -DATABASE=database [-OUTFILE=filename]
--USER=SA_USER -SERVER=SERVER -PASSWORD=SA_PASS [-debug] -TYPE=Sybase|ODBC
DESCRIPTION
Track size of a table. Has a variety of uses. Output format
<time> <rows> <num new rows> <new rate per minute>
OPTIONS
if --ADDDATE then it will put a yyyymmdd to end of the file
monitor_errors.pl
read files in monitor_data and compares to config file
|
DESCRIPTION
This program reads files from monitoring_data that have .err extesnsions and it will parse the standard format for specific types of errors. These errors will then be handled.
monitor_user.pl
utility to debug servers
|
DESCRIPTION
Check out your server! Should audit as much as it can!
USAGE
monitor_user.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
mssql_attachscript.pl
create a set of sql statements to attach or detach your databases.
|
DESCRIPTION
When moving your sql server files around, it is a pain to create the scripts to attach and detach the files. This program will create a set of scripts capable of attaching files once you detach the database and the sql statements needed to detach them as well.
The --GO option puts go's between lines
USAGE
Usage: mssql_attachscript.pl [--DATABASE=db] --DEBUG --SYSTEMS=system[,system] [-DETATCH] [-GO]
--GO will print go's
mssql_cycle_eventlog.pl
Cycle SQL Server Error Log Files
|
USAGE
Usage: mssql_cycle_eventlog.pl --DAYS=days --SYSTEM=system --DEBUG --BATCHID
mssql_datapath_report.pl
paths for your sql server
|
DESCRIPTION
TBD
USAGE
Usage: mssql_datapath_report.pl --SYSTEMS=system[,system]
mssql_gem_setup_report.pl
analyze your sql servers and produce registration report
|
DESCRIPTION
Discovers your configuration based on interfaces and osql -L and then it will produce a report detailing what is registered and the server status.
mssql_jobstatus_rpt.pl
Print a job status report
|
DESCRIPTION
You may specify all systems from the pc config file or specifiy the systems to use.
USAGE
Usage: mssql_jobstatus_rpt.pl --STDALARM --SYSTEMS=system[,system] --DOSHRINK
OPTIONS
--SYSTEMS : comma separated list of systems. Otherwise does em all
mssql_logmaintplan.pl
read logmaintplan table in mssql and save as heartbeats
|
DESCRIPTION
Reads the logmaintplan table in mssql - which stores sql server job states and stores the results as heartbeats.
mssql_scheduled_job_rpt.pl
get scheduled jobs report
|
DESCRIPTION
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 your password file as per Repository.
In other words... if you wish to run a program to remove user paulr on a server you might type
adduser -SSYBASE -Usa -PXXX -x -upaulr
but with this utility, you could run that command on ALL your servers in one fell swoop with:
AllSrv adduser -x -upaulr
As you can see, the -U,-P, and -S parameters are added automatically
USAGE
Usage: ./AllSrv Parameters
Generic program to run on all servers. If you type
"./AllSrv MY_PROG OTHER_PARM"
you will end up executing
"MY_PROG -SSRV -PPASS -UUSR OTHER_PARM"
for all the servers in your password file.
mssql_shrinklogs.pl
shrink data/log files on sql server
|
DESCRIPTION
Shrinks your data/log files on your sql servers. This is a much faster shrink than you would get if you were using a shrink database. The program also allows you to shrink your data files if you pass --DATATOO.
You may specify all systems from the pc config file or specifiy the systems to use. Be careful tho. This will confuse the tran log dump/load programs if you pass the --DUMPTRANNOLOG option to truncate the log.
USAGE
Usage: mssql_shrinklogs.pl --DATATOO --STDALARM --DEBUG --SYSTEMS=system[,system] -
-DOSHRINK --DUMPTRANNOLOG --MAXSIZE=MB --DATABASE=DBLIST
OPTIONS
--DUMPTRANNOLOG : dump tran with nolog prior to shrinking (dangerous?)
--DOSHRINK : do the shrink
--SYSTEMS : comma separated list of systems. Otherwise does em all
--MAXSIZE : maximum file size for files to shrink (does not to larger ones)
optdiag_analyze.pl
anlyze optdiag output and reorg rebuild if cluster ratio sucks
|
USAGE
optdiag_analyze.pl -USER=SA_USER [-FILE=file] [--NOSTDOUT] --OUTFILE=File --OPTDIAG=optdiag -DATABASE=db -SERVER=SERVER -PASSWORD=SA_PASS [-debug] [-REBUILD] [-NOEXEC]
DESCRIPTION
Analyzes optdiag output and produces a fragmentation report as per the algorithms described at 2006 techwave. Need to validate this by lock scheme. Uses the following keys:
Data page cluster ratio: Index page cluster ratio: Data row cluster ratio: Space utilization: Large I/O efficiency:
parse_a_file.pl
Parse a file into parts
|
DESCRIPTION
Very useful for finding problems with your bcp files...
USAGE
Usage: parse_a_file.pl -FFilename -Rrow_delimeter -Ccol_delimter
NOTES
parse_hosts.pl
Parse hosts files that have been collected
|
USAGE
parse_hosts.pl -FFile -DDir -hd -OFile -BBatchDesignator
passwd_rpt.pl
multi server report sa/sso/oper roles utility
|
DESCRIPTION
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 file for this to work. See documentation on Repository.pm library.
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
***********************************************************
* 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
ping_server.pl
utility to ping databases
|
USAGE
ping_server.pl -SERVER=list -NOLOG
ping_server.pl
SYNOPSIS
pings all servers in your password files. Returns number of servers that cant be connected to. Prints status of your servers. Has no embedded error handling.
IF -SERVER is passed, you can specify a comma separated list of servers
process_statistics.pl
compare server to saved statistics file(s)
|
USAGE
process_statistics.pl -USER=USER -SERVER=SERVER -PASSWORD=PASS
-DATABASE=DATABASE -ACTION=action -FILE1=file -FILE2=file ...
-TITLE1=title -TITLE2=title...
DESCRIPTION
if ACTION=save
saves statistics for a database in a file
if ACTION=compare
compares statistics to files. Hilighting divergences
purge_unix_files.pl
purge old files
|
DESCRIPTION
lots of directories need space management. This provides you with that facility. It is mostly useful under unix and uses ftp to do the removal. use purge_old_win32_files on windows.
USAGE
purge_unix_files.pl --hours= --days= --dir= [--del] [--done] --ctlfile=
A control file can be used to automate multiple systems. See conf/unix_cleanup.dat.sample. Set up all your ctrlfile and then use UnixCleanup scheduled GEM task.
you should pass -del, -done, or it wont do much
-del deletes files over <days>
-done only work on files ending with .done over that age = implies -del
purge_old_win32_files.pl
purge old files
|
DESCRIPTION
A core dba task is directories space management. This excellent utility provides you exactly that. And it does it right - parsing your files so you control exactly what the program does. It is mostly useful under windows.
USAGE
purge_old_win32_files.pl --hours= --days= --dir= [--del]
[--done] [--donothing] [--report=file] --compress=progname --ctlfile=<file>
ARGUMENTS
--ctlfile=file : A control file can be used to automate multiple systems. See conf/win32_cleanup.dat.sample. The controlfile basically contains the arguments to this program
# DR01 Cleanup
#
--days=2 --dir=//ad001/e$/logshipbuffer --del --done
--days=10 --dir=//ad001/e$/logshipbuffer --del
--hours or --days : controls the time for the comparison
--del or --donothing : what do you want to do - delete the files or dont
--done : only work on files with a .done or .done.gz or .done.Z extension. These files are created by our log shipping system AFTER they have been applied successfully to the log shipping target.
-compress= compresses the files
-versions=num : keep only num versions of the file. Because backup files are normally datestamped, this option removes all sequences of 2+ numbers from the filename
query_compare.pl
Compare Query Results in Two DB's
|
DESCRIPTION
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
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.
SAMPLE RUNS
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
#!/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
SYNOPSIS
rebuild.pl - Rebuild Hashbang And use lib statements
COPYRIGHT
Copyright (c) 2002-3 By Edward Barlow
All Rights Reserved
Explicit right to use can be found at www.edbarlow.com
This software is released as free software and should be shared and enjoyed
USAGE
Usage: G:\dev\AHMR52~F\bin\rebuild.pl [--UNSET] --OVERRIDE --LIBDIR=DIRECTORY,DI RECTORY --FILENAME=FILENAME,FILENAME --DIRECTORY=DIRECTORY,DIRECTORY [--DEBUG] - -PERL=PERL
USE --UNSET to reset your perl to default hashbangs
DESCRIPTION
Resets the perl hashbang lines at the top of the file. If the first line starts with a hashbang #! then the next part is a command to reformat_file. This can either be of the format
#!/usr/local/bin/perl
or if you pass --UNSET
: # use perl
eval 'exec perl -S $0 "$@"'
if $running_under_some_shell;
These lines are reformated correctly based on the perl in your path or based on the perl you pass
USAGE
rebuild.pl file file file...
or
rebuild.pl *
or
rebuild.pl -D.
or
rebuild.pl -u
to undo changes to generic format
--OVERRIDE will work on restricted file types
NOTES
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:
: # use perl
eval 'exec perl -S $0 "$@"'
if $running_under_some_shell;
skips files named configure.pl or rebuild.pl. Those should always be full path name to the perl executable using the above syntax.
DESCRIPTION
copies all files passed on command line to save/filename.yymmdd
USAGE
./redate.pl file1 file2 file3 file4
SYNOPSIS
reperl.pl - Rebuild Hashbang And use lib statements
COPYRIGHT
Copyright (c) 2002-3 By Edward Barlow
All Rights Reserved
Explicit right to use can be found at www.edbarlow.com
This software is released as free software and should be shared and enjoyed
DESCRIPTION
Resets the perl hashbang lines at the top of the file. If the first line starts with a hashbang #! then the next part is a command to reformat_file. This can either be of the format
#!/usr/local/bin/perl
or
: # use perl
eval 'exec perl -S $0 "$@"'
if $running_under_some_shell;
These lines are reformat_fileted correctly based on the perl in your path
USAGE
reperl.pl file file file...
or
reperl.pl *
or
reperl.pl -D.
or
reperl.pl -u
to undo changes to generic format
NOTES
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:
: # use perl
eval 'exec perl -S $0 "$@"'
if $running_under_some_shell;
skips files named configure.pl or reperl.pl. Those should always be full path name to the perl executable using the above syntax.
replicator.pl
simple sybase replicator
|
DESCRIPTION
Simple database replicator. Implemented by a trigger on source tables which saves data into a separate replication database. The replicator.pl program sweeps data from this replication database to the destination server / database, and should always be running during production hours. A configuration file contains the details of the replication, so multiple replications can occur, run by different processes. replicator.pl contains the code to define the replication tables and triggers as well as performing the actual replication.
you probably should read the whole code line before implementing ... its not complex.
HOW IT WORKS
The source tables have triggers on them for insert, update, and delete which save data into a replication database in a table of the same name with an r_ prepended. As an example, the mydb..mytable table would have triggers that take changes and put them into the repldb..r_mytable table from where replicator.pl would sweep them to targetdb..mytable. The triggers supports user defined triggers on the source table. When replicator.pl -d is run, it defines triggers which start with r_, but does not modify and user generated triggers (which should not start with r_). The user is responsible for modifying their own triggers to conform to the replication spec. Triggers in the source db with a name starting with r_ are system managed. The replication db must be dedicated to the replication system and will contain tables identical to the source tables with the addition of a char(1) r_trig_type column, a char(1) r_xfer column, and a timestamp r_trig_time column (first 3 columns).
The replicator moves data from the replication db to the target db in a safe manner using transactions.
USAGE
./replicator.pl [-c configfile ] -snl
-c cfg,cfg configuration file name (default replicate.cfg)
multiple configuration files allow multiple replications
-D define the triggers if they are not allready there
-u uninstall
-S starts the configuration
-l active configuration and statistics report
-d debug mode
one of -d,S,or l must be specified
DEFINE OPTION
The -D option will create the tables and triggers necessary to run the system. Triggers are created in the source database and tables are created in the replication database (using the information found in the config file).
The -d option presumes that objects starting with r_ are system created and can be managed internally. It also presumes that if there are no triggers on a table, it is safe to create one.
User triggers will need to be changed to add statements that manage the r_tablename table in the replication database.
select r_trig_type=' ',r_xfer=' ',r_trig_time=NULL,*
into orig_table
from yyy..orig_table
where 1=2
You are welcome to put your own triggers in with your own replication definition so long as you dont start the trigger name with r_.
SPECIAL COLUMN DEFINITION
The r_trig_type column contains the following values
"I" - inserted
"D" - deleted
"d" - deleted rows in update trigger
"i" - inserted rows in update trigger
The r_trig_time column is a timestamp (insert null into this column)
The r_xfer column should be 'x' for columns being transfered and ' ' otherwise.
CONFIG FILE
The configuration file is in replicator.cfg which has the following format:
[
SRCSRV = DSQUERY
SRCUSR = LOGIN
SRCPASS = PASSWORD
SRCDB = DB
SRCREPL = DB
DESTSRV = DSQUERY,DSQUERY,DSQUERY
DESTDB = DB,DB,DB
TABLES = tbla,tblb,tblc
POLLTIME= num_seconds
]
ORIG_DB is the original database and is only used by the -d create option.
TODO
Must create index on tables for optimal tuning (hmmmm how...)
Destination server EXPECTED to be consistent when startup. This might be a problem.
Sanity Checker?
rev_db.pl
Reverse engineer sql to individual files
|
DESCRIPTION
Reverse engineer objects to individual files. If you dont pass a set
USAGE
rev_db.pl -USA_USER -SSERVER -PSA_PASS -DDB_LIST -OOutDir [-E] [objects...]
-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
Reverse Engineer To Files... Extensions based on object name
sample_db_script.pl
sample utility
|
USAGE
sample_db_script.pl -USA_USER -SSERVER -PSA_PASS -debug
DESCRIPTION
[ insert your description here ]
setup.pl
install patch to an application as appropriate
|
DESCRIPTION
This is a customized package. Installations must be customized for each server / environment you wish to work with.
USAGE
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.
IMPLEMENTATION DETAILS
This program lives in the root directory of the patch release. Database objects get installed based on the directory right above them.
CUSTOMIZATION
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.
- %SERVER_DIR
Target directory for installation
- %SERVER_DSQUERY
DSQUERY of server for db objects
- %SERVER_LOGIN
LOGIN of server for db objects
- %SERVER_PASS
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.
REQUIREMENTS
Requires do_release_func.pl and syb_func.pl to be installed either in the current directory, the subdirectory utilities, or the LOCAL root.
TO DO
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
the gui
Perhaps it should keep track of individual object upgrades in its own database
On a per server/db/object/version basis.
showplan_analyze.pl
showplan analyzer
|
DESCRIPTION
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
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
FUNCTIONS
THE FOLLOWING SUBROUTINES HAVE BEEN DEFINED AT THIS POINT:
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()
size_monitor.pl
monitor database (size or performance)
|
USAGE
size_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
Monitors a db size or summary statistics.
SAMPLE OUTPUT
size_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
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.
SUMMARY
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.
INSTALLATION
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.edbarlow.com
PARSER RULES
The following are the rules the parser uses to store data from sp_sysmon into variables. The actual subroutine name is s_read_sysmon_inline.
*) ignore blank lines or lines with -----------
*) a line of === is a new section. The first 2 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
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.
USAGE
sysmon_analysis.pl [-s$SYBASE] -SDSQUERY -UUSER -PPASSWORD [-ifile -hayc -ttime -ofile]
-d : debug mode
-i file : file name of sp_sysmon input (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)
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.
ThresholdManager.pl
Manage Sybase Thresholds
|
DESCRIPTION
This program is a warning system for out of space conditions on sybase. It does this by installing sybase threshold stored procedures named sp_gemthresholdaction_ that allow you to monitor the peak usage percentage of each of the segments in your database. This is an effective method of monitoring your sybase log and data space.
USAGE
Usage: ThresholdManager.pl --ACTION=INSTALL|UNINSTALL|REPORT|CHECK
[--SERVER=xxx] [--DEBUG] [--OUTFILE=file]
If server is not provided it will loop through all servers
ACTIONS
if --ACTION=INSTALL will install into the server
if --ACTION=UNINSTALL will remove created thresholds from the server
if --ACTION=CHECK will check your installation
if --ACTION=REPORT will create a report
- will print data for --REPORT_HOURS hours
- will only show rows > --TIME_THRESH hours (default 24x7 hours)
- will print HTML output if --HTML is passed
NOTES
ONLY USE THIS UTILITY IF YOU DO NOT ACTIVELY USE DATA SEGMENTS... CURRENTLY SEGMENTS ARE NOT CHECKED FOR AND WE ONLY DEAL WITH logsegment and default. The reports will be off if you use segments.
Basically what this tool does is create a bunch of thresholds in your databases that are very simple one liners, saving info into a table in sybsystemprocs. The goal here is so that you can see when the logsegment is getting full. It also allows you to see if the default segment is getting full - which might be useful in the case where data is rapidly growing and shrinking during the course of the day and you cant rely on the once per day space monitoring. Fundamentally this is a light weight method of doing that... active space monitoring is a big performance hog and runs the risk of system table locks - while this does not.
So you an INSTALL the stuff easily into all your sybase servers and then rely on the console to report peak usages and to make recommendations on how to grow your log files.
After you extend your databases, rerun the -ACTION=INSTALL procedure to reset your thresholds.
=end
trunctate_db.pl
truncate database utility
|
DESCRIPTION
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.
USAGE
USAGE: truncate_db.pl -UUSER -SSERVER -PPASS -DDB
-E TABLES (comma separated list of tables to EXCLUDE)
-I TABLES (comma separated list of tables to INCLUDE)
-n (noexec)
win32_backupreport.pl
SQL Server Backup Report
|
DESCRIPTION
Scans the event log and creates a backup and restore history for a server. There are two types of reports here. The first is the Backup Report, which simply prints backup details for a server. The second is a Restore report which is more cmoplicated and shows restore mappings to the primary server
USAGE
Usage: PC_BA~RP.PL --OUTFILE=file --HTMLFILE=file --SYSTEM=system --HOURS=hours --USER=sa --PASSWORD=password --SRVNAME=ODBCID --NOSTDOUT --REPORT= --PRIMARY= --DEBUG --HTMLDETAIL=file --LISTEVENTS
EXAMPLE
To see whats going on in a server
win32_backupreport.pl --SYSTEM=S1 --HOURS=96
To put results in a file
win32_backupreport.pl --SYSTEM=S1 --HOURS=96 --NOSTDOUT --OUTFILE=my_report.txt
Since the ascii report sucks - an heml file can be generated by
win32_backupreport.pl --SYSTEM=S1 --HOURS=96 --NOSTDOUT --HTMLFILE=my_report.html
If you are using log shipping, a new report opens up. Say your server is BACKUP1 which does log loads from PRIMARY1
win32_backupreport.pl --SYSTEM=BACKUP1 --PRIMARY=PRIMARY1 --HOURS=96 --NOSTDOUT
--HTMLFILE=main.html --REPORT=restore --HTMLDETAIL=details.html
The above creates a linked pair of reports - with a summary in HTMLFILE and details of the shipping and loading in HTMLDETAIL. Note that some times your report will be out of sync... the Behind time explains this - its possible that the lsn's dont match up intentionally.
=end
win32_diskspace.pl
NT Diskspace Report
|
DESCRIPTION
Uses Win32 Libraries to check out a list of disks on remote NT servers.
USAGE
Usage: win32_diskspace.pl --OUTFILE=file --LOGALARM --ERRFILE=file --SYSTEMS=system[,system] [--DEBUG] --ALARMWHO=netsend[,netsend] --ALARMTHRESH=pct --WARNTHRESH=pct --SLEEPTIME=seconds [--NOSTDOUT]
OUTFILE: Output Report File
LOGALARM: Save Heartbeats using MlpAdmin
ERRFILE: Output Report for just errors
SYSTEMS: List of systems to work on - comma separated
ALARMWHO: Comma separated list of netsend addresses to send to (built in)
ALARMTHRESH: Alarm percent
WARNTHRESH: Warning percent
SLEEPTIME: Seconds in a loop
DEBUG: Debug
NOSTDOUT: Dont print stuff to stdout
CONFIG
Reads win32_password.cfg (from sw directory or one above it) file which contains lines as follows
ADSRV060 C$ D$ E$ F$
ADSRV088 C$ D$ E$ F$
ADSRV089 C$ D$ E$ F$
ADSRV056 C$ D$ E$ F$
PLATINUM C$ D$ E$ F$
ADSRV061 C$ D$ E$ F$
win32_eventlog.pl
NT Diskspace Report
|
DESCRIPTION
Uses Win32 Libraries to check out eventlogs on remote NT servers. Output is saved using alarming functions.
USAGE
Usage: win32_eventlog.pl --OUTFILE=file --ERRORFILE=file --SYSTEM=system
--HOURS=hours --SERVICE=service[,service] [--CONFIGFILE=filename]
[--DEBUG] [--LOGEVENTS] [--LOOPSLEEPTIME] [--NOINFOMSGS]
OUTFILE: Output Report File
ERRORFILE: Output Report for just errors
SYSTEM: List of systems to work on - comma separated
HOURS:
DEBUG: Debug
LOGEVENTS: Save Heartbeats using MlpAdmin
LOOPSLEEPTIME
CONFIG
Reads Repository data file which looks like:
IGNORE LIST
The program reads the __DATA__ section at the bottom of the program to find out messages that it can ignore by service, severity, and error number. Each service can specify by Error/Warning/Info whether it wants to ignore all messages (wildcard %) or a specific list of messages to ignore.
win32_get_file.pl
Get File From Each Win32 System Out there
|
DESCRIPTION
Used to copy file to local system - usually into html_output dir.
This is an excellent program and can be used as a template for other little batches you wish to use.
It probably will mostly be called from other batches - the one i have in mind right now is to get win32 task scheduler jobs log files from all my systems. That would be something like win32_get_file.pl --INDIR=/winnt,/windows --INFILE=SchedLgu.txt I will also add one for hosts.
win32_ipconfig_report.pl
Create Ip Configuration Report For Windows
|
USAGE
win32_ipconfig_report.pl
win32_service.pl
service manager for NT
|
USAGE
Usage: win32_service.pl Usage: win32_service.pl service Usage: win32_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...
DESCRIPTION
Show NT services. Filters service if service passed (one argument). Starts & Stops services if second argument is STOP or START.
USAGE
show all services
win32_service.pl
start service
win32_service.pl START service_name
stop service
win32_service.pl STOP service_name
filter services
win32_service service_name
show running/stopped services
win32_service running|stopped
win32_shutdown.pl
shutdown windows system
|
DESCRIPTION
Shutdown an windows system. Only works if you have administrator priviliges.
win32_system_report.pl
Create System Reports For Windows
|
USAGE
win32_system_report.pl -SYSRPT=file --SWRPT=file
This output is documentation for the SQL Technologies GEM UTILITIES GUIDE.
copyright © 1998-2008 By
SQL Technologies