################################################################## # # # DBPowerSuite # # # # Written by: Brian Ceccarelli # # 4605 Woodmill Run # # Apex, NC 27539 # # Phone (919) 815-0126 # # FAX (888) 746-6982 # # ceccareb@talussoftware.com # # # # Operating Systems: Solaris 10, 9, 8 # # Red Hat Linux Enterprise 5, 4, 3 # # Red Hat 9, 8, 7.2 # # Mac OS/X for Power PC # # ODBC Level: ODBC 3.5 Compliant # # Sybase Adaptive Servers: 15.0, 12.5.1, 12.5, 12.0, 11, 10 # # Sybase Client Libraries: Open Client 15, Open Server 12.x # # # # Oracle: 11g, 10g, 9i, 8i, 8.0 # # Postgres: 8.x, 7.x # # MySQL: 5.x, 4.x, 3.x # # # # Last Updated: October 20, 2007 # # # # DBPowerSuite (C) 1986-2007 Talus Software # # All rights reserved # # # # # # This software (both binaries and source) is owned by # # the copyright holder. You may not duplicate it, duplicate # # and then modify the code, nor redistribute the original # # or the modified version in any way without the expression # # written permission of the copyright owner. The copyright # # owner reserves the right to charge you royalties or # # license fees. This software is distributed in the hope # # that it will be useful, but without any warranty, without # # even the implied warranty of merchantibility or fitness # # for a particular purpose. # # # # The software in PS/os/xxxxx/odbc, where xxxxx # # is the operating system name, is unixOBDC or iODBC, # # products distributable under the GNU public license. # # Also in this directory is the MySQL ODBC driver. # # # # The software under PS/os/xxxxx/MySQL Server, where # # xxxxx is the operating system name, are the fully compiled # # MySQL 5.0.19 server and client--all distributable under # # the GNU public license. # # # ################################################################## An acronoym for DBPowerSuite is 'PS'. From now on, we will use PS to refer to DBPowerSuite. This README file has 6 sections: 1. Intro 2. Installation 3. Examples of How to Run Programs 4. Writing Your Own PS Program 5. Troubleshooting and Technical Support 6. Description of Each Program ################################################################## # # # SECTION 1: INTRO # # # ################################################################## DBPowerSuite is two things: 1. A bundle of extremely powerful and useful ready-to-run programs for database management automation. 2. A high-speed powerful C++ API so that you can write your own platform independent programs. We designed DBPowerSuite so that you can run any program within a command script. You can run the programs from any shell, including Perl. Every DBPowerSuite program provides a useful exit code-- an exit code that always discerns between a successful and failed run. You can fully automate your database management processes, and do so easily. The features of DBPowerSuite programs are two fold: 1) ODBC features, and 2) Sybase specific features. 1. For ODBC Users: ------------------- * psOLoader loads a delimited text file into a table at any ODBC data source. * psGCopyTableData creates the scripts to transfer tables' data from one ODBC data source to another. Now you can transfer Oracle data to Sybase! * psOPing pings an ODBC data source as well as tells you the hundred or so characteristics and properties of the ODBC driver manager, driver and data source. * psOSQL is an interactive or batch command session to any ODBC data source. You can pipe SQL commands into psOSQL and redirect the results elsewhere. psOSQL can . . . * Can connect to different servers during a single session. * Can run SQL scripts from a session. * Can run SQL scripts which nest other SQL scripts. * Exits with a meaningful return code. 0 = successful. Non-zero means that either client-library or the server raised an error. Therefore, you can write operating system scripts which process SQL commands against your database and you can easily check the psOSQL return code for success or failure of your SQL. No more 'grepping' the log file. * Can automatically send e-mail upon detecting errors. * Can exit immediately upon receiving an error. * Can exit unconditionally and successfully when it sees 'exit psOSQL' comming from a SQL 'print' command. * Has a batch mode, interactive mode, or can execute a SQL command right on the operating system's command line. * Can write both isql-style output and string-delimited output. You can define your own column delimiter strings and row terminator strings. * Can comment results with timestamps, no timestamps or abstains from writing comments altogether. * Can direct output to unlimited number of files during a single session. * Can e-mail command batches during a session. * Maintains a history of command batches which you can recall and execute at any time. * Can perform up to 10 string substitutes on the fly on an incoming file. * Supports image and text types without size limitation. * Can list the databases, tables, views, procedures and users at the database server. * And much much more. 2. Sybase DBAs additionally can . . . -------------------------------------- * Generate web pages describing every table in your database. * See a list of devices and how much each database takes up on each device. This is great for planning new databases. * See exactly how much space every table and index occupies on every disk and segment. This will tell you how much space you have left on any particular segment and which tables or indices are the pigs. * Instantly spot invisible indices, duplicate indices, lack of proper segmentation, empty tables, missing referential integrity, examine cross-database constraints, cache bindings. * Instantly spot tables or indices that have orphaned fragments--fragments that do not occupy space on the current object's segment. This is great for detecting bad dumps and loads and data you think should be elsewhere. * Instantly set permissions on thousands of objects to thousands of users. * Copy table schemas or individual objects (like stored procedures) from one database to another. * Copy table data from Sybase to Sybase, Oracle to Sybase, vice-versa, or to and from any ODBC data source. * Copy indices, referential integrity constraints, triggers and keys from one database to another. * You can also just drop the constraints, the triggers, the keys, etc. from a user. * Find missing tables, procedures and views. Ever wonder in what database you created a particular table? Now you can find it instantly. * Check bulk copy files for index duplicates and data type integrity before bulk copying them into Sybase. * Automatically check a database's referential integrity after data has been bulk copied in. (Bulk copy ignores referential integrity contraints.) * Dump all the transaction logs or databases on a server with a single command. * Update the statistics on all tables in all databases on a server with a single command. * List all the spids on a Sybase server in UNIX "top" fashion. * Safely shutdown an ASE server. psKillProcesses can terminate every spid or Backup Server connection, do a checkpoint on all databases, then shutdown your server. psKillProcesses can also selectively terminate sessions belonging to a specific login or application name. * Use psSQL as a replacement for isql. psSQL does everything psOSQL does (see above). The most handy feature of psSQL is that psSQL returns exit codes. When psSQL returns 0, all your SQL commands executed successfully. If psSQL detects any warning or error, psSQL exits with a non-zero code. You can also use psSQL to connect to Sybase's Open Servers, Replication servers and Gateways. * And much much more. 3. Sybase Replication Server Administrators can . . . ------------------------------------------------------- * Automatically generate or drop replication definitions and subscriptions. * See the current state of replication definitions and subscriptions and programs. * Have a means of automatically waiting for a set of replication defintions and subscriptions to reach a desired state. This README file (see below) has a description of every DBPowerSuite program. For your peace of mind, no DBPowerSuite program writes directly to a database server. DBPowerSuite generates scripts that do. You in turn, run the scripts. This way, you can use a text editor to check what exactly will happen before it happens. For example, psDropTables drops tables by generating a Korn shell script with an embedded psSQL session. To actually drop the tables, you run the Korn Shell script. And like all DBPowerSuite programs, even the generated scripts return useful exit codes. 4. Developers: ---------------- * Platform Independence. A program you write using the DBPowerSuite API instantly works on Solaris, Linux and Macs. There is also a DBPowerSuite program called psPortCode. For ANY Unix, it takes only one day make all of DBPowerSuite runnable on the new platform. The only thing holding us back from porting to AIX or HPUX, is the lack of hardware to test on. * Underneath the ODBC programs is DBPowerSuites's ODBC class library. You can use this class library to build your own ODBC applications. And remember, all DBPowerSuite's APIs are thread-safe. To create threads and manage locks, use the UThread and the Mutex classes (see UThread.h and Mutex.h). The underlying APIs automatically invoke these classes. * The API features thread-safe database access via ODBC or via Sybase Open Client library. The API provides an easy means to write Sybase Open Servers. * You can also use the utility layer API to easily write *any* portable multithreaded application. Your applications do not have to be database apps. ################################################################## # # # SECTION 2: INSTALLATION # # # ################################################################## Installation Prerequisites -------------------------- * You will need 300 MB of free disk space to install PS. After installation, PS running environment requires about 100 MB. * If SPARC Sun: You must be running Solaris 8 or better. Make sure you have installed the latest patches for Solaris. For 8, you must have installed the 8_Recommended patches. * If Macintosh: You must be running Mac OS X Tiger or newer. * If Linux, Intel-based or Alpha-based: We have tailored builds for Red Hat Linux 7.2, 8, 9, RHEL3, RHEL4 and RHEL5. PS also works with Suse, Mandrake and probably every other version of Linux. All you need to be concerned about is the kernel version of your Linux and its version of the gcc/g++ libraries. On older versions of Linux, you must have installed the gdbm and the Korn shell (pdksh package) RPMs. The Korn shell RPM does not always install by default. You can use GNOME's RPM Manager to install any missing RPMs. The RPMs are on the Linux installation CDs. * For Sybase: 1. Your Adaptive Server Databases must be either version 15.x, 12.x, 11.x or 10.x. PS works with them all. 2. DBPowerSuite comes bundles with the Sybase client specific to your platform. Red Hat Enterprise 3, 4, and 5 and Red Hat 9 come with the Sybase 15 client. Red Hat 7 comes with the Sybase 12.5 client. Alpha Linux comes with the Sybase 11 client. Mac comes with the 12.5 client. 3. One PS program, psListSegments, only works with a Sybase 12.5 Adaptive Server when you are using the Sybase version 12.5 Open Client/Open Server libraries. psListSegments with other versions of the Sybase Open Client library does work with 10.x, 11.x, or 12.0 Adaptive Servers. * For Oracle: 1. DBPowerSuite has programs, scripts and an API geared especially for Oracle. Your Oracle instances must be either version 10g, 9i, 8i or 8.0. PS works with them all. 2. If you are using DBPowerSuite and you plan on using an ODBC driver which requires the Oracle client software, then you must have installed the Oracle client software on your computer. * For Microsoft SQL Server: 1. DBPowerSuite does have programs and an API geared especially for Microsoft SQL Server. DBPowerSuite works with SQL Server 7, SQL Server 2000 and SQL Server 2005. 2. While the syntaxes of the Transact SQLs of Microsoft SQL Server and Sybase ASE are very close, they are not identical. The DBPowerSuite generic database programs (the psG* programs) in DBPowerSuite know this. * For Postgres: 1. DBPowerSuite comes with the unixODBC driver manager and Postgres ODBC driver. 2. You do not have to install Postgres on your computer for DBPowerSuite to connect to a remote Postgres database. 3. Certain versions of DBPowerSuite come with a build in version of the Postgres 8.2.1 server and client. * For MySQL: 1. DBPowerSuite comes with the unixODBC driver manager and MySQL ODBC driver and MySQL thread-safe client. 2. DBPowerSuite comes with a complete MySQL 5.0.19 server. Check the PS/os/MySQL for the binaries. 3. You do not have to install MySQL on your computer for DBPowerSuite to connect to a remote MySQL database. * For ODBC. unixODBC: -------- There are no preinstallation requirements. DBPowerSuite comes bundled with unixODBC (a free GNU package) with Postgres and MySQL ODBC drivers. There are other drivers in the package if you dare to try them. There are several builds of unixODBC that appear with DBPowerSuite on Linux. Under the PS/os/Linux/odbc directory, there are RH7, RH9, RHE3, RHE4 and RHE5. RH7 - unixODBC for Red Hat 7.2, 8 RH9 - unixODBC for Red Hat 9 RHE3 - unixODBC for Red Hat Enterprise Linux v3 RHE4 - unixODBC for Red Hat Enterprise Linux v4 RHE5 - unixODBC for Red Hat Enterprise Linux v5 There is one version of unixODBC, under PS/os/Solaris/SparcSol8 that comes with DBPowerSuite on Solaris. We build that one using the Sun Forte C++ compiler. That means that you do not have to install any GNU products on your SPARC computer for DBPowerSuite to work. Data Direct Technologies Connect ODBC ------------------------------------- While unixODBC contains good ODBC drivers for the free databases MySQL and Postgres, it does not appear to have good production quality drivers for commerical databases such as Sybase, Oracle or MS SQL Server. Excellent database drivers for Sybase, Oracle DB2, Progress and MS SQL Server are manufactured by Data Direct Technologies (http://www.datadirect-technologies.com) for just about every platform. DBPowerSuite supports the Data Direct Technology's 32-bit ODBC-Connect product. If you are an OEM of Data Direct, then you may be building yourself "branded" drivers. DBPowerSuite also works with Data-Direct "branded" drivers. You can tell DBPowerSuite the name of your brand password so that you can use DBPowerSuite apps run with your branded drivers. We do not bundle the Data Direct Technology drivers with DBPowerSuite because we would be violating Data Direct's copyright. Data Direct does not make drivers for MySQL or Postgres. That is why DBPowerSuite comes with unixODBC. * For DEVELOPERS using the DBPowerSuite API: If Sun: 1. We built DBPowerSuite using the Sun C++ 5.3 compiler. You will be required to compile your PS apps using a Sun compiler. 2. There are two versions of DBPowerSuite for Sun. The POSIX-thread and the Solaris-thread compiled versions. If you are not planning to write applications using the DBPowerSuite API, then it does not matter which version you download. 3. The third party libraries to which you link your apps should match PS's thread model. Do NOT mix thread models. Do NOT mix POSIX threads with Solaris threads. 4. If you are developing Sybase Open Server 12.5 applications, the Sybase 12.5 Open Server API is POSIX. Therefore, you should be developing with POSIX DBPowerSuite. DBPowerSuite has a full OO-interface for Sybase Open Server. 5. If you are developing Sybase Open Server 12.0 applications, Sybase 12.0 Open Server API is Solaris threads. Therefore, you should be developing with Solaris-threads DBPowerSuite. If Linux: 1. We build an Intel Linux version of DBPowerSuite using Red Hat Enterprise 4's bundled g++ compiler. 1. We build an Intel Linux version of DBPowerSuite using Red Hat Enterprise 3's bundled g++ compiler. This build also works with Red Hat's Fedora Core 2 and 3. 2. We build an Intel Linux version of DBPowerSuite using Red Hat 9's bundled g++ compiler. 3. We build an Intel Linux version of DBPowerSuite using Red Hat 7.2's bundled g++ compiler. This works on Red Hat 8 and 9 as well. 4. We build a flavor of DBPowerSuite using Red Hat Linux 7.2's g++ 2.96 compiler on a 64-bit Alpha-based computer. So if you have one of those speedy expensive 64-bit based Compaq alpha computers hanging around as a door stop, you can now put it to good use. Installation Procedure ---------------------- 1. Untar the download. Go the parent directory where you want to install DBPowerSuite. In this example, I'll use the /home/ceccareb directory. cd /home/ceccareb For PS-Solaris-7.9-4.gtar.gz, gunzip and gtar extract the code. Use standard GNU gunzip and gtar: gunzip PS-Solaris-7.9-4.gtar.gz gtar xvf PS-Solaris-7.9-4.gtar For PS-Solaris-7.9-4.tar.gz, tar extract the code. Use Mac's tar command: tar --gunzip -xvf PS-Solaris-7.9-4.tar.gz For PS-Solaris-7.9-4.tar.Z, uncompress and tar-extract the code. Use standard Sun uncompress and Sun tar: uncompress PS-Solaris-7.9-4.tar.Z tar xvf PS-Solaris-7.9-4.tar All tar files yield the exact same code. Both will create a directory called: ./PS So now you have a directory called /home/ceccareb/PS 2. Change your login shell environment. Here is a working example of a .tcshrc which installs the DBPowerSuite environment. (It is PS/package/os_scripts/unix/OS.tcshrc.) 1. #!/bin/tcsh 2. ####### Set your Locale 3. 4. setenv LC_ALL en_US 5. 6. ####### PATH and LD_LIBRARY_PATH 7. 8. setenv PATH .:/bin:/sbin:/usr/bin:/usr/X11R6/bin:/usr/local/bin:/usr/X11R6/bin 9. setenv LD_LIBRARY_PATH 10. 11. 12. ###### Sybase. 13. 14. source /opt/sybase/Sybase12_5.csh 15. 16. ###### Postgres 17. 18. source /opt/Postgres/Postgres.csh 19. 20. ###### DBPowerSuite 21. 22. setenv DBPOWERSUITE /home/ceccareb/PS 23. unsetenv ODBC 24. unsetenv ODBCINI 25. unsetenv ODBCSYSINI 26. unsetenv PS_UNIXODBC 27. # setenv PS_UNIXODBC RH7 28. # setenv PS_UNIXODBC RH9 29. # setenv PS_UNIXODBC RHE3 30. # setenv PS_UNIXODBC SparcSol8 31. 32. source ${DBPOWERSUITE}/package/os_scripts/unix/psEnvironment.csh 33. source ${DBPOWERSUITE}/package/os_scripts/unix/psPrompt.csh 34. 35. setenv DSQUERY beast 36. setenv SYB_OC_VERSION 15001 37. setenv SYB_OS_VERSION 15001 38. setenv SYB_BLK_VERSION 15001 39. setenv SYB_USER ceccareb 40. setenv SYB_PASS mypassword 41. setenv DBNAME admanager 42. 43. setenv ODBC_DSN XenaPostgres 44. setenv ODBC_USER ${USER} 45. setenv ODBC_PASS ${USER} 46. setenv ODBC_DATABASE ${USER} 47. setenv ODBC_ATTR 48. 49. setenv PS_MAKE_OPT 58 50. 51. ###### Aliases. 52. 53. alias ll 'ls -alF' 3. Set your internationalization locale. This locale must be supported by DBPowerSuite, the Sybase Client, and the ODBC drivers. While DBPowerSuite adapts to just about anything, the Sybase Client or the ODBC drivers may not. For example, on Solaris computers, en_US.UTF-8 is the default locale, but the Sybase 12.5.1 client does not understand this. On Red Hat Linux computers operating in the United States, LC_ALL and LANG should = en_US.utf8 4. Change your environment to include path to sendmail. (Line 8) Many of DBPowerSuite programs can use 'sendmail'. This program is found in different directories on different operating systems, even in different directories of different versions of the same operating system. It is usually in /usr/bin, but not all the time. The directory you find in it should be in your PATH. 5. Make sure your .login, .cshrc or .profile include the Sybase, Postres, MySQL and/or Oracle environments. (Line 14, Line 18) If you are using Sybase, and you haven't set Sybase's own required environment, you must change your .login, .cshrc or .profile to include the Sybase environment before including PS's environment. PS has convenient .csh and .sh scripts for Sybase, Oracle, Postgres and MySQL under PS/dbaccess/os_scripts/DB_NAME. You should copy these scripts to your database's software home directory. For example, a people can "source" Postgres.csh and thus enable the Postgres environment in their shell. Most likely, you will have to modify these scripts to your own specific environment. PS also has convenient start up and shut down .csh scripts for Sybase, Postgres, MySQL and Oracle located in the same directories. PS also has a S80Sybase script which you can put in your UNIX root run state environment. This script allows you to automatically start up and gracefully shutdown your Sybase ASE when the computer boots and shuts down. 6. Change your environment to include the PS environment. (Lines 22-49) Include the PS environment AFTER you have defined the Sybase, Oracle, Postgres and/or MySQL environment(s). ---------------------------------- Korn or Bourne Shell Lovers Do This: ----------------------------------- In your .profile, add unset ODBC unset ODBCINI unset ODBCSYSINI DBPOWERSUITE=/home/ceccareb/PS export DBPOWERSUITE . ${DBPOWERSUITE}/package/os_scripts/unix/psEnvironment.sh - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ODBC Drivers If you want to use your own ODBC drivers, you must set the environment variable ODBC *before* you include the DBPowerSuite environment. The environment variable ODBC must point to the directory where ODBC is installed. If you do nothing, DBPowerSuite activates its own internal ODBC environment. If DBPowerSuite finds the Data-Direct Technologies driver directory, PS/os/xxxxxx/odbc/odbc42c (where xxxxx is the OS name), then DBPowerSuite includes this ODBC environment into your shell. If the Data-Direct ODBC directory does not exist, DBPowerSuite includes the appropriate unixODBC environment into your shell. If both Data-Direct ODBC and unixODBC directories exist, DBPowerSuite defaults to the Data-Direct environment. You can tell DBPowerSuite to use the unixODBC environment by using one of the lines 27-33: PS_UNIXODBC=RHE5; export PS_UNIXODBC for the RH Enterprise 5 PS_UNIXODBC=RHE4; export PS_UNIXODBC for the RH Enterprise 4 PS_UNIXODBC=RHE3; export PS_UNIXODBC for the RH Enterprise 3 PS_UNIXODBC=RH9; export PS_UNIXODBC for the RH 9 PS_UNIXODBC=RH7; export PS_UNIXODBC for the RH 7.2, RH 8 PS_UNIXODBC=SparcSol8; export PS_UNIXODBC for the Sparc Solaris 8, 9, 10 You can only have 1 ODBC environment existing at the one time. You cannot mix Data Direct ODBC with unixODBC or unixODBC with other flavors of unixODBC. Note Lines 23-25. unset ODBC (used by DBPowerSuite) unset ODBCINI (used by Data-Direct Technologies) unset ODBCSYSINI (used by unixODBC) UNsetting the ODBC environment before calling DBPowerSuite environment is a wise thing to do. Certain UNIXes and certain window managers do not start your environment from "scratch" when you launch a new window. If you do NOT unset the ODBC variables, then the new window may use whatever values for ODBC that the parent window is using. You may get something that you did not wish for. See "CONFIGURING ODBC" below. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Line 33. DBPowerSuite also comes with a nice prompt for Korn Shell. It looks like: hostname:[23] /home/ceccareb % To use this style prompt for a Korn shell session, add . ${DBPOWERSUITE}/package/os_scripts/unix/psPrompt.ksh to your .profile. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - For Bourne Shell (as opposed to Korn shell) users, if you want a slightly better prompt than the default prompt ($), DBPowerSuite also comes with prompt for Bourne Shell. It looks like: hostname$ To use this style prompt for your session, add . ${DBPOWERSUITE}/package/os_scripts/unix/psPrompt.sh to your .profile. ----------------------------------------------------- C Shell (csh) or TC-Shell (tcsh) Shell Lovers Do This: ----------------------------------------------------- In your .cshrc (or .tcshrc), add (Line 32-49) setenv DBPOWERSUITE /home/ceccareb/PS source ${DBPOWERSUITE}/package/os_scripts/unix/psEnvironment.csh - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ODBC Drivers If you want to use your own ODBC drivers, you must set the environment variable ODBC *before* you dot the DBPowerSuite environment. The environment variable ODBC must point to the directory where ODBC is installed. If you do nothing, DBPowerSuite activates its own internal ODBC environment. If DBPowerSuite finds the Data-Direct Technologies driver directory, PS/os/xxxxxx/odbc/odbc41c (where xxxxx is the OS name), then DBPowerSuite includes this ODBC environment into your shell. If the Data-Direct ODBC directory does not exist, DBPowerSuite includes the unixODBC environment into your shell. If BOTH Data-Direct ODBC and unixODBC directories exist, DBPowerSuite defaults to the Data-Direct environment. You can force DBPowerSuite to use either unixODBC environment by changing line 24: setenv PS_UNIXODBC RHE4 for RH Enterprise 5 setenv PS_UNIXODBC RHE4 for RH Enterprise 4 setenv PS_UNIXODBC RHE3 for RH Enterprise 3 setenv PS_UNIXODBC 9 for RH 9 setenv PS_UNIXODBC 1 for Solaris, RH 7.2, 8 setenv PS_UNIXODBC 3 for RH 7.2, 8 g++/gcc 3.2.1 Unfortunately, you can only have 1 ODBC environment existing at the one time. Note Lines 23-25. unsetenv ODBC (used by DBPowerSuite) unsetenv ODBCINI (used by Data-Direct Technologies) unsetenv ODBCSYSINI (used by unixODBC) Unsetting the ODBC environment before calling DBPowerSuite environment is a wise thing to do. Certain UNIXes and certain window managers do not start your environment from "scratch" when you launch a new window. If you do NOT unset the ODBC variables, then the window manager may use whatever values for ODBC that the parent window is using. You may get something that you did not wish for. Also see "CONFIGURING ODBC" below. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - DBPowerSuite also comes with a nice prompt for C Shell. (Line 33) It looks like: hostname:[23] /home/ceccareb % To use this style prompt for your session, add source ${DBPOWERSUITE}/package/os_scripts/unix/psPrompt.csh to your .cshrc or .tcshrc. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 7. Set the default Sybase ASE Server which any Sybase clients connect to. (Line 35) This one is a standard Sybase environment variable. It is not specific to DBPowerSuite. 8. Set the Sybase Open Client, Open Server and Bulk Copy Behavior. (Lines 36-38) The DBPowerSuite SYB_OC_VERSION, SYB_OS_SERVER and SYB_BLK_VERSION environment tell DBPowerSuite programs which version of the Sybase hehavior to use when connecting to and from Sybase clients and servers. DBPowerSuite Sybase Open Clients use SYB_OC_VERSION. DBPowerSuite bulk copy programs (psBCP) use SYB_BLK_VERSION. DBPowerSuite Open Servers use SYB_OS_VERSION to connect to a server. You find valid values for SYB_OC_VERSION and SYB_OS_VERSION in $SYBASE/include/cspublic.h. You find valid values for SYB_BLK_VERSION in blkpublic.h. After you install DBPowerSuite, you can see what values DBPowerSuite is currently using by running psHelp. 9. Set DBPowerSuite default login variables for Sybase (Lines 39-41), and ODBC (Lines 43-47). These are optional, but extremely handy. Use them. Most PS programs require the name of the database server (-S), the user name (-U), the user's password (-P) and database name (-D) as command line arguments. You don't have to type these out everytime you run a PS program. In the absence of a command line argument, PS uses a specific environment variable. Here is the list of environment variables and what they represent: Environment Variable Description Argument ------------------------------------------------------------ DSQUERY Sybase Adaptive Server -S SYB_USER Sybase User Name -U SYB_PASS Sybase Password -P DBNAME Sybase Database to use -D ODBC_DSN ODBC Data Source -S ODBC_SERVER ODBC Server Name -V ODBC_USER ODBC User Name -U ODBC_PASS ODBC Password -P ODBC_ATTR ODBC Additional Attributes -A DBPowerSuite does not use ODBC_DATABASE at this time. ODBC_ATTR is the variable that has the data source specific attributes for connection purposes. These attribute/value pairs depend on your ODBC driver. Note that line 39 says DB=admanager This attribute works for certain Data Direct drivers. It also works for the Postgres drivers. Specifying the database is important to Microsoft SQL Server and Sybase. Each server can handle 32,767 databases. For Oracle, you would change ODBC_ATTR to SID=yourOracleSID The Oracle listener knows an Oracle instance only by its SID. (Server instance ID) A single Oracle instance is a single database. You can have many instances running on a single computer, but Oracle is such a resource hog that multiple instances is not practical. For a list of limitations of Oracle, see the Oracle Facts pages at http://www.talussoftware.com. 10. Set the value of the DBPowerSuite build environment variable. Line 49. This setting is only necessary if you are writing C++ programs using the DBPowerSuite API. This number is the build ID. It is part of the download file name. For instance, 56 means the build for Red Hat Linux Enterprise 3. The download file name for Red Hat Linux is PS-Linux-6.7-56.gtar.gz. You can always tell what version and what build you are running by running the program "psWhatVersion". 11. Logout and login. Make sure you do not get errors. 12. Install the DBPowerSuite License. You should have received a welcome e-mail from Talus Software. This welcome e-mail is your license. Make a simple text file from this e-mail. Say you called it license.txt To install the license, psInstallLicense -I license.txt You will get a message saying that your license is "successfully installed." To make sure everything went right, do psHelp If you get a list of programs, then you have successfully installed your license. 13. Configure ODBC. At the operating system prompt, type: psSetODBCDrivers.csh This program tells the ODBC driver manager where the ODBC drivers are for your specific installation. Now comes your part. You have to edit the ODBC configuration files to point to your own specific data sources. DBPowerSuite looks at the environment variables ODBC, ODBCINI and ODBCSYSINI. DBPowerSuite programs look for the ODBC driver manager and drivers in ${ODBC}/lib. The driver manager must be called libodbc.so. DBPowerSuite looks at ODBCINI for the system_odbc.ini file for Data Direct. DBPowerSuite looks at ODBCSYSINI for the odbc.ini file for unixODBC. DBPowerSuite comes bundled with ready-to-go unixODBC ODBC driver manager and the Postgres and MySQL ODBC thread-safe drivers. They are located in PS/os/xxxxx/odbc/unixodbc (xxxxx is the operating system name). DBPowerSuite does not come with the Data Direct ODBC Connect product. You can get this product from Data Direct. http://www.datadirect-technologies.com. You can download an eval copy. ODBC Connect has drivers for Sybase, Oracle, DB2, Microsoft SQL Server, Informix, Progress, dBase, XML, etc. Data Direct ----------- Many companies have DataDirect ConnectODBC. For those companies, you may have "branded" your drivers. Therefore, you know somewhere you must supply DBPowerSuite with the and of your DataDirect license file as well as its password. In DBPowerSuite, you supply the name DataDirect's license file and its password in DBPowerSuite's system parameter file: utilities/parameters/parameters.txt. Use a text editor to look at this file. Look at the parameters that start with "merant". These "merant" parameters are solely for DataDirect's ODBC drivers. (Merant is the old name for DataDirect.) Put your own values for the license file name and password in this file. Save the file. Run btLoadParameters. Once done, all the DBPowerSuite ODBC programs will use these values. Depending on the ODBC drivers you are using, you may also need to specify other parameters. You change the system_odbc.ini file. You tell Data-Direct's driver manager where system_odbc.ini is by setting the ODBCINI environment variable. unixODBC -------- unixODBC looks at the $ODBCSYSINI variable to see where odbc.ini resides. odbc.ini looks at odbcinst.ini for the location of its drivers. Use a text editor on these files to add and change data sources. Doesn't unixODBC have a GUI to edit data sources? Yes. These programs are in the DBPowerSuite directory PS/os/YOUR_OS/odbc/unixODBC/bin. However, they will not work. You will have to use a text editor to edit the odbc.ini and odbcinst.ini files. The GNU build process which makes these programs has a linking mistake. The build process links the unixODBC/bin programs with the /unixODBC/lib libraries using absolute path names. That is wrong! Because this happens, these programs only work if the libraries have not moved from their original installation directory. (You can make a nasty symbolic link which equates my build directory /home/ceccareb/unixodbc to the location of your PS/os/YOUR_OS/odbc/odbcunix directory, and then these programs will work.) Just as a point, notice that it doesn't matter which directory on your computer contains the DBPowerSuite 'PS' directory. You can tar up your PS directory, move it to any computer to any directory, change your DBPowerSuite environment variable, and everything runs as if nothing happened. Pointing DBPowerSuite to ODBC ----------------------------- psSetODBCDrivers.csh is the script you will run to tell DBPowerSuite the location of ODBC. Before you run this, you must know how to make changes to odbc.ini and odbcinst.ini for unixODBC, or system_odbc.ini for Data-Direct. Changing these files is specific to your ODBC system, not DBPowerSuite. See your ODBC documentation for this. To customize PS to point to your ODBC data sources, manually edit the *_odbc.ini_template files. You will find this file in ${DBPOWERSUITE}/os/YOUR_OS/odbc/*. After you edit the files, run psSetODBCDrivers.csh This command reads your template file and creates the real *odbc.ini files. psSetODBCDrivers.csh and the templates work together so that whereever you move PS to, you can just run psSetODBCDrivers.csh and the newly created *odbc*.ini will point to the new driver locations. THAT'S ALL! ----------- To make sure it is installed, login and logout, then type: psHelp This will give you a list and brief descriptions of all the DBPowerSuite available to you. It will also tell you the values of important Sybase, ODBC, Oracle, Postgres, Java, DBPowerSuite and UNIX environment variables. ################################################################## # # # SECTION 3: EXAMPLES OF HOW TO RUN PROGRAMS # # # ################################################################## EXAMPLE ------- psListTables -help This will tell you the syntax of the command AND which environment variables it looks for (e.g., DSQUERY, SYB_USER, SYB_PASS, SYB_DATABASE | DBNAME). EXAMPLE ------- psListTables -SEEYORE -Ubrianc -Ppassword -DVLUDB -Flist_of_tables.out This command will create a file called "list_of_tables.out". It will contain a list of all the tables in database "VLUDB" on Adaptive Server EEYORE. The list includes column definitions, index information, referential integrity constraint information, segment information, and named cache information. EXAMPLE ------- psListTables -S EEYORE -U brianc . . . . A blank space between the "-S" and "EEYORE" also works. EXIT CODES ---------- All DBPowerSuite programs return meaningful exit codes. An exit code of 0 means a successful run of the program. Any other exit code means the program didn't work as you expected. To show what the exit code means, run psShowMessage -C{exit_code} If the DBPowerSuite program generates a script, it is a runnable Korn shell script. The generated Korn shell script also returns exit codes with the same conventions as the DBPowerSuite programs which generated them. This consistent exit code convention enables you to easily include DBPowerSuite in your shell scripts and while powerfully checking if any errors occurred. psSQL vs isql -------------------- A fast way to run psSQL in interactive mode is to type: pssql Likewise, a fast way to run psOSQL in interactive mode is to type: psosql pssql and psosql are aliases for 'psSQL -OP' and 'psOSQL -OmP' respectively. The DBPowerSuite exit code behavior is especially useful when you need to run a SQL script within in a UNIX shell program. Without DBPowerSuite, you would run the SQL script in an 'isql' session. But as you know, isql doesn't return any valid return codes, nor does it stop processing your script when an error occurs. So you never know if Sybase actually performed your SQL correctly. You can't stop Sybase in the middle of your script either. You don't have to limit yourself to that anymore! Now you can run the same script through psSQL. psSQL is a glorified replacement of isql. And by checking the psSQL exit code, you will instantly know if anything went wrong. psSQL also has keyword subsitutions and e-mailing upon error. For more help about running psSQL, type psSQL -help And within psSQL, type 'help' and see a list of internal commands. For example: 1:1> help ################################################################## # # # SECTION 4. WRITING YOUR OWN PS PROGRAM # # # ################################################################## WRITING and COMPILING YOUR OWN DBPowerSuite API-based PROGRAM -------------------------------------------------------------- Your DBPowerSuite installation contains all the C++ include files, Makefiles and libraries for you to be able to write, compile and link your own programs. To compile an application based on the PS API, you must have already installed DBPowerSuite. Your compilation requires environment variables set previously by psEnvironement.csh or psEnvironment.sh. In addition to those environment variables, you need to set only one more environment variable. You must set PS_MAKE_OPT. In your .cshrc or .profile, add . . . setenv PS_MAKE_OPT n (TC or C Shell lovers) - or - PS_MAKE_OPT=n (Korn or Bourne shell lovers) export PS_MAKE_OPT Replace 'n' with one of the following values: n Platform ------------------------------------------------------------- 1 SPARC Solaris 32-Bit Build (Non-Posix for Solaris 2.6+) 3 SPARC Solaris 32-Bit Build (Non-Posix for Solaris 2.8+) 4 SPARC Solaris 32-Bit Build (Posix for Solaris 2.8+) 46 Intel Linux (Red Hat Linux 6.2, g++ 3.1.1, Sybase 10) 47 Intel Linux (Red Hat Linux 6.2, g++ 3.1.1, Sybase 12.5) 48 Intel Linux (Red Hat Linux 6.2, g++ 2.91, Sybase 10, 11) 49 Intel Linux (Red Hat Linux 6.2, g++ 2.91, Sybase 12.5) 50 Intel Linux (Red Hat Linux 7.2, g++ 2.96, Sybase 12.5) 51 Intel Linux (Red Hat Linux 7.2, g++ 3.1.1, Sybase 12.5) After a proper install, and after you set PS_MAKE_OPT to your choice, you can compile your application. Applications Must Begin With an Instantiation of the class DBPowerSuite ----------------------------------------------------------------------- Look at the demonstration program btOTestThreads.cc (ODBC) or btTestThreads.cc (Sybase) in PS/dbaccess/demo. These are good examples of DBPowerSuite applications. The first thing you must do in your program is instantiate a DBPowerSuite object. This will initialize the underlying API. If you do not do this, your program will not get very far. Error Message Codes and Text ----------------------------------------------------------------------- The Messages class uses the codes and text in PS/utilities/parameters/messages.txt to print error messages. All return codes from API functions return one of these codes. The codes match those in psReturnCodes.h. You can add your own codes between 30000 and 32766. Once you add your codes to messages.txt, run program psLoadMessages. psLoadMessages reads messages.txt and creates the DBPowerSuite message catalog. Classes and Function Which Are Available to You --------------------------------------------- To see the ODBC and Sybase class headers, check out PS/dbaccess/include To see the utility classes and function prototypes, check out PS/utilities/include You can also view all the classes and function of the API by pointing your web browser to DBPower To see the specific classes and function prototypes unique to your operating system environment, check out PS/os/Solaris/include (for Solaris) PS/os/Linux/include (for Linux) The dbaccess layer lays on top of the utilities layer. The utilities layer lays on top of the OS layer. When you write a program: 1. Use classes and functions only under dbaccess and utilities. 2. Use ONLY basic datatypes provided in PS/os/YOUR_OS/include/sysYOUR_OSplatform.h If you do these two things, your application will port without modification to any other platform. Study sysSolarisPlatform.h or sysLinuxPlatform.h. You will learn to . . . Do this: ps_int32 i, j, k; Not this: int i, j, k; ---------- When forming file names do this: ps_char string[PS_FILE_NAME_BUFFER_LEN]; uFormat(string, "%croot%cmyfile.txt", PS_DIRECTORY_SYMBOL, PS_DIRECTORY_SYMBOL); Not this: char string[128]; sprintf(string, "/root/myfile.txt"); Because: The directory symbol changes between UNIX and the Microsoft world. ps_char changes automatically into UNICODE when compiling for international character sets. char does not. uFormat converts to UNICODE usage when compiling for international character sets. sprintf does not. PS_FILE_NAME_BUFFER_LEN defaults to the largest file name size for your OS. ---------- When choosing a datatype for a simple on-and-off switch, do ps_int8 sw; Not: ps_char sw; Because: ps_char is not a single byte in UNICODE. ps_char is specifically for text. Use ps_int8 for a single byte or a ps_int8 array for a stream of binary bytes. ------------ When formatting a 64 bit integer, do this: ps_int64 n; uFormat("The value of n = %_ps_uint64.\n", n); Not this: uFormat("The value of n = %llu.\n", n); (Sun 32-bit) Because: The token used to display a 64 bit integer varies greatly between operating systems and between 32 and 64-bit compilers. Exception Handling ------------------ The PS API does NOT throw exceptions. So don't use try and catch blocks. The objects keep track of their own state. Instead, all functions and methods return completion codes. Completion Codes ---------------- Any function which is not a simple C++ accessor returns a 'ps_int32' completion code. If the completion code = 1, then the function completed SUCCESSFULLY. Yes, 1 = successful. The DBPowerSuite API uses positive logic. The API returns "completion" codes, not "error" codes. A list of the all completion codes are in /utilities/include/psReturnCodes.h. Remember, positive is good. Yes is good. TRUE is good. When writing a PS-API based program, adhere to this standard. (The convention that of functions returning 0 for good, even though widely used, is illogical. Some UC Berkeley flunkey came up with that 20 years ago and introduced chaos and a negative universe. VAX/VMS always used positive logic. Things are always clearer in a positive universe. Your programs are easier to read. Try it. You will like it.) Exit Codes ---------- We become hypocrits when it comes to the exit code for an entire program. A '0' exit code means the program ran successfully. Any other value means an error. This convention was set by some Berkeley student in the 1970s who had a negative attitude. While in the PS universe, everything is positive and good. But when it comes to the real world, PS adapts the 1970's Berkeley hacker's convention. Write your PS-based application such that it always returns a meaningful exit code. All PS programs do this. When you adhere to this standard, a person can write a perl or shell script which runs your your program and which easily checks for its success status. We designed the PS architecture to bubble up completion codes, even when they come from deep within a nested function. Because of this, a script writer gets tremendous pleasure out of invoking any PS program from a script. Only PS can run SQL against any database and tell the invoking script if there was an error. Messages -------- All the error messages you see come from the messages file. For every completion code in psReturnCodes.h, there is a message in the message file. The text associated with each code is in .../parameters/messages.cat. messages.cat is a X/Open message catalog. PS creates the messages.cat file by running the UNIX program gencat on the file messages.xpg. psLoadMessages created messages.xpg from reading the text file messages.txt. The starting point is messages.txt. The ending point is messages.cat. When programming, you use the Messages classes to read messages from the message.cat catalog. X/Open messages catalog is an internationalized file. Makefiles --------- Look at the Makefile templates in /dbaccess/make and /utilities/make. We build all PS programs using those templates. For examples of using the templates, look at /dbaccess/demo and /utilities/demo. You don't have to change any of the templates. You just include them in your own makefile. We compile all PS programs and libraries using GNU make. You must use GNU make too. You must not use Sun 'Make'. Sun 'Make' does not have the features to run a GNU-style Makefile. When we build PS programs and libraries, we always include the Makefile header: PS/utilities/make/commondefs.mh To make application development easy for you, you should include this common definitions header for Makefiles. Examine this make file header carefully. All PS makefile templates include this make file header. You can make your life simple if you take the time to understand what this header does. Other Compilation Notes: ----------------------- When using commondefs.mh and setting PS_MAKE_OPT properly, you don't worry about the following. But just to let you know what is going on . . . The -noex flag -------------- You will find that we use the -noex flag on the Sun 4.2 compile line in commondefs.mh because 1) no PS code throws exceptions, and 2) this make option compiles with the Sun C++ 4.2 compiler. Therefore, we can specify the -noex flag. Removing the -noex makes the binaries smaller. PS_MAKE_OPT=1 automatically takes care of this. If you are using the Sun Workshop 5.x compilers, you must take the -noex out of utilities/make/commondefs.mh. Sun 5.x compilers generate object code whose own function calls throw exceptions. PS_MAKE_OPT=4 automatically takes are of this. Stronger Data Typing in GNU C++ and Sun Solaris 5.x Compilers ------------------------------------------------------------- You will find that the GNU and Sun 5.x compilers impose stronger data typing than the Sun 4.x C++ compiler. You just can't use "void pointers" or string literals like before. 5.x compilers do not like string literals (e.g., "literal text") passed as "char" arguments. GNU and 5.x compilers require "const char". This is at it should be. Clean Compile--No Warnings/No Errors ------------------------------------ We take pride in the fact that all of DBPowerSuite compiles and links without errors or warnings on all platforms. That includes the SPARC Sun 4.X and 5.x compilers with or without POSIX coompliancy and the GNU g++ 2.96 compiler on Linux. Your application should compile as cleanly. Sybase Open Server Applications ------------------------------- Sybase Open Server 12.5 applications require several things for compiling. Note *12.5*. 1. If on a Sun, you must run your Open Server on Solaris 8 or better. (ASE 12.5 works only on Solaris 8+). 2. You must compile your code with POSIX compliancy. In Linux, POSIX is not an issue. Linux is POSIX compliant by default. However, on a Sun, you must use PS_MAKE_OPT=4 to get POSIX compliancy. Sybase Open Server 11.9 and 12.0 applications on a Sun use Solaris threads. The above 3 requirements are not valid for 11.9 and 12.0 open servers. If your Sun open server app is built on Sybase 11.9 or 12.0 open server library, then your app is a Solaris threads app, and therefore, is not POSIX. Any Solaris-threaded program requires -D_REENTRANT and -mt instead of -D_POSIX_C_SOURCE on the compile line. Irregardless of POSIX or platform, you need to link all your Sybase Open Server apps with libpsOSVThreads. POSIX Compliancy ---------------- Sun --- 1. To get POSIX compliancy for a PS app, you must use -D_POSIX_C_SOURCE=199506L as an option on the Sun C++ 5.x compiler. You also link your app to libpsPosixThreads. PS_MAKE_OPT = 4 takes care of all this for you. Linux ----- 1. No special arguments are required. The GNU compiler does POSIX compliant code by default. You also link your app to libpsPosixThreads. ################################################################## # # # SECTION 5. TROUBLESHOOTING # # # ################################################################## TECHNICAL SUPPORT ----------------- If you have a question, comment or problem, please e-mail me: brian@talusmusic.com If you have a problem, first check to see if one of the problems you are seeing is one I have listed below. If not, tell me what version of the PS you are running: psWhatVersion Send me the error messages if any. If you are daring, send me a trace too. To turn on trace, set the operating system environment variable: For C shell: setenv PS_TRACE_MASK to 0xFFFFFFFFFFFFFFFF For Bourne or Korn shells: PS_TRACE_MASK=0xFFFFFFFFFFFFFFFF; export PS_TRACE_MASK and run the program again. The trace information goes into the program's log file. (-F always specifies an output log file.) PSW, there are 16 'F's. The trace mask is a 64-bit integer under the covers. COMMON PROBLEMS --------------- Some problems are not caused by the PS programs themselves, but rather to Sybase or the way you have configured your Sybase server or operating system. Here's a list of the problems we have seen that are like this: Abnormal Program Exit Due to "ct_fetch(). Item #21 overflowed" Problem ----------------------------------------------------------------------- Sometimes when a program queries a database to get a list of tables, you will see a Sybase error message: "ct_fetch() failed. Item #21 overflowed." followed by the DBPowerSuite message: This is actually not a BrianTools program problem but an internal Sybase statistics problem on the particular database's system tables. Once in a blue moon, if ever, you may see that message. To fix the situation permanently, you must update all the statistics on that database's system tables. You do this by running psUpdateStatistics -Dbad_database -Osx -Fupd.ksh Cores Due to Sybase "Total Memory" --------------------------------- There is a high probability that your core dump is not caused by the PS program itself. We know that core dumps will occur within the Sybase Open Client library when you have not configured the Sybase server with enough "total memory." We have seen this happen with psListProcesses. For some reason, the Sybase client library will cause psListProcesses to core when you have connected to a ASE server configured with insufficient memory. psSQL will work but psListProcesses will not. psListProcesses makes the ASE server build sysprocesses--a dynamic table. If the ASE server does not have enough memory, then the server will corrupt the creation of sysprocesses and send corrupt data back to the psListProcesses. This causes psListProcesses to core within a Sybase Open Client function. Cores Due to Insufficient Stack Size ------------------------------------ Some programs in DBPowerSuite require a large stack size. We have seen PS programs core when the computer's stack size limit is set too low. We have kept the stack size requirements to under 8 MB (the default on Solaris). But your own environment may be limiting it further. To see you current stack soft and hard limits (csh), type: ulimit -a To increase the stack size to 16MB, you must first su to root and change root's soft stack limit: ulimit -S s 16384 (16384 KB is 16 MB) and then go back to the shell you are running DBPowerSuite, and then increase its stack size soft limit: unlimit stacksize which brings your current shell's stack limit up to the size of root's. If you don't change root's stack size first, your current stack size will not change. PS programs will core without any warning if your stack size is not large enough. Make sure your stack size is large enough before sending us an e-mail. Internal Limits --------------- There are times when PS programs will not core, but simply stop and say: 2001.08.20 15:30:22 ER07030 The number of XXXXXXXXX exceeds the maximum allowed. PS has internal maximums for the number of tables in a database, number of procedures in a database, indexes per table, etc. If you get a message like this, e-mail us. We will bump up the values. When you bump up the values, you may also have to bump up the stack size. ################################################################## # # # SECTION 6. DESCRIPTION OF EACH PROGRAM # # # ################################################################## DOCUMENTATION ------------- There is a set of web pages describing all the programs of DBPowerSuite. Under your DBPowerSuite directory, the top web page is called doc/web/programs/PROGRAMLIST.html. From this web page, you can see the syntax and usage of every program. There is a set of web pages desribing all the classes and functions of DBPowerSuite--the entire API. Under the DBPowerSuite directory, the top web page for the API documention is called doc/web/API/API.html. From API.html, you can see the names, descriptions and usages of all classes and functions in DBPowerSuite. You can use the 120+ programs of DBPowerSuite right from the begnning. These 120+ programs are all based on the API foundation. DBPowerSuite comes with all the include files and libraries so that you can write your own DBPowerSuite applications In the end, the best way to learn how to run PS programs is to experiment with them. There is nothing you can run which modifies data. All programs are read-only programs. The programs which update a database, only do so by first generating a Korn shell script. Then you run the Korn shell script manually. A list of all the PS programs follow. ######################################################### # # # List of DBPowerSuite Programs # # # ######################################################### DBPowerSuite 7.9-4 for SPARC Solaris 32-Bit Jun 18 2009 21:03:20 'It's the gray areas that matter.' ------------------------------------------------------------------------------ Help ------------------------------------------------------------------------------ For More Help, Type -help ------------------------------------------------------------------------------ ODBC Tools ------------------------------------------------------------------------------ psOCopyTableData Writes scripts to move data from one ODBC data source to another psODropIndices Writes SQL script to drop indices psOListIndices Lists the index names at an ODBC data source psOListProcedures Lists the procedure and function names at an ODBC data source psOListTables Lists the table names at an ODBC data source psOListViews Lists the view names at an ODBC data source psOHelp Lists only the ODBC and utility programs psOLoader Uploads a field-delimited text file to an ODBC table psOPing Tests if you can connect to an ODBC data source psOSQL Executes SQL by file or interactive session on a data source psOTestReconnects Tests the ODBC drivers to see if they can reconnect to the DSN psOTestThreads Tests the ODBC drivers and client libraries for thread-safety ------------------------------------------------------------------------------ Generic Database Tools ------------------------------------------------------------------------------ psGAddCheckConstraints Writes a SQL script to add check constraints psGAddRIConstraints Writes a SQL script to add referential integrity constraints psGChangeTablespaces Writes a SQL script that changes tablespaces on tables or indices psGCheckRIConstraints Writes a SQL script to check referential integrity constraints psGCheckRules Writes a SQL script to check check constraints and rules psGCopyTableData Writes scripts to move data from one ODBC data source to another (req. Sybase) psGCountRows Writes a SQL script to count the rows in specific tables psGDeleteRIViolators Writes a SQL script to delete rows violating referential integrity constraints psGDeleteRuleViolators Writes a SQL script to delete rows violating check constraints and rules psGDropCheckConstraints Writes a SQL script to drop check constraints psGDropRIConstraints Writes a SQL script to drop RI constraints psGDropProcedures Writes a SQL script to drop stored procedures or functions psGDropTables Writes a SQL script to drop tables psGDropViews Writes a SQL script to drop views psGenerateCCDatatypes Write C++ code for DBDatatype::dtLoadDefinitions psGReindex Writes a SQL script which rebuilds indices. psGSegmentSQL Adds segment/tablespaces to Power Designer SQL scripts psGSetOracleTableLogging Turns archive logging on/off for Oracle tables psGSetOracleTableParallel Turns parallelism on/off for Oracle tables psGTruncateTables Writes a SQL script to truncate tables psGUpdateStatistics Writes a SQL script to update statistics for tables psGWriteSQL Writes a SQL script using a list of objects and a printf() format psIsReservedWord Tells you if your text is a database reserved word ------------------------------------------------------------------------------ Sybase ------------------------------------------------------------------------------ Sybase Adaptive Server 15.x, 12.5x, 12.0, 11.5, 11, 10 Tools ------------------------------------------------------------------------------ psBCP Bulk copies a file into a table psBcpIntoTable Writes a ksh script which drops indexes, runs bcp, readds indexes psBindDefaults Writes a ksh script which binds columns to created defaults psBindRules Writes a ksh script which binds columns to created rules psCheckBulkCopyFile Checks whether an ASCII bulk copy file can be loaded psCheckDB Runs dbcc checkalloc(db, fix) on databases psCheckpoint Performs a checkpoint in every write-enabled database psCheckRIConstraints Writes a ksh script which checks tables for referential integrity psCheckRules Writes a ksh script which checks whether tables abide by their rules psCopyDefaults Writes a ksh script which copies defaults from 1 db to another psCopyIndices Writes a ksh script which copies tables' indices psCopyKeys Writes a ksh script which copies sp_primarykeys and sp_foreignkeys psCopyProcedures Writes a ksh script which copies procs from 1 db to another psCopyRIConstraints Writes a ksh script which copies ref. integrity constraints psCopyRules Writes a ksh script which copies rules from 1 db to another psCopySchema Writes a ksh script which copies a schema from 1 db to another psCopyTableData Writes a ksh script which copies table data from 1 db to another psCopyTables Writes a ksh script which copies table column definitions psCopyTriggers Writes a ksh script which copies triggers from one schema to another psCopyUserTypes Writes a ksh script which copies user-defined datatypes psCopyViewKeys Writes a ksh script which copies sp_primarykeys and sp_foreignkeys on views. psCopyViews Writes a ksh script which copies views from one schema to another psDropDefaults Writes a ksh script which drops defaults psDropDevices Writes a ksh script which drops devices psDropIndices Writes a ksh script which drops tables' indices psDropKeys Writes a ksh script which drops sp_primarykeys and sp_foreignkeys psDropProcedures Writes a ksh script which drops stored procedures psDropRIConstraints Writes a ksh script which drops ref. integrity constraints psDropRules Writes a ksh script which drops rules psDropSegments Writes a ksh script which drops segments psDropTables Writes a ksh script which drops tables psDropTriggers Writes a ksh script which drops triggers psDropUserTypes Writes a ksh script which drops user-defined datatypes psDropViewKeys Writes a ksh script which drops sp_primarykeys and sp_foreignkeys on views psDropViews Writes a ksh script which drops views psDumpDatabase Dumps a database psDumpServer Wrties a ksh script which dumps all databases of a Sybase server psDumpTransactionLog Dumps a database's transaction log psEstimateSpace Estimates the sizes of tables & indexes, grouped by segment psEstimateSpaceFile Generates a list of tables for input to psEstimateSpace psFindProcedure Finds a stored procedure amongst all databases in an ASE server. psFindTable Finds a table amongst all databases in an ASE server. psFindView Finds a view amongst all databases in an ASE server. psGenerateCCDefines Generates a C++ header #-defining the max lengths of columns psGenerateDumpName Generates a file name for a DB or transaction log dump psGenerateRIMap Generates a list of tables and outgoing RI constraints psGenerateRITriggers Generates ref. integrity triggers based on sp_foreignkeys psGenerateSegmentMap Generates a list of tables and their segments psGenerateTable Generates C++ classes which invoke generated stored procedures psGetSegment Displays a table's segment name psKillProcesses Kills processes on a Sybase ASE server psListDefaults Lists all defaults created with 'create default' psListDevices Lists all ASE server physical devices & the databases living on them psListInterfaces Lists all the servers in the interfaces file psListProcedures Lists all stored procedures psListProcesses Lists CPU, I/O, and memory usage for ASE spids in realtime psListRules Lists all rules created with 'create rule' psListSegments Lists all segments' table & index space allocations psListTables Lists all tables w/ indices, constraints, etc. psListTablesForWeb Generates a HTML page for each table definition psListTriggers Lists all triggers psListUsers Lists all users in a database psListUserTypes Lists all user-defined datatypes psListViews Lists all views psLoadDatabase Loads a database psLoadTransactionLog Loads a database's transaction log psOpenServer An open server which has RPCs to run UNIX commands and e-mail psPingServer Checks if a Sybase server is up psSetPermissions Writes a ksh script which sets permissions on objects and users psSQL Executes SQL or other commands on a Sybase ASE or Open server psTestThreads A test multithreaded Sybase Open Client program psTranslateSchema Translates a Sybase schema into Oracle scripts psTruncateTables Writes a ksh script which truncates tables psUnbindDefaults Writes a ksh script which unbinds columns from created defaults psUnbindRules Writes a ksh script which unbinds columns from created rules psUpdateServer Writes a ksh script which updates statistics for all databases psUpdateStatistics Writes a ksh script which updates statistics in a single database Replication Server Tools ------------------------------------------------------------------------------ psDropRepDefs Writes a ksh script which drops replication definitions psDropSubscriptions Writes a ksh script which drops subscriptions psCheckRepDefs Checks & waits for rep defs to exist or drop psCheckSubscriptions Checks & waits for subscriptions to attain a specified state psGenerateRepDefs Generates replication definitions psGenerateSubscriptions Generates subscriptions from rep defs psListRepDefs Lists the replication definitions in a RSSD psListSubscriptions Lists the subscriptions in a RSSD psMarkTablesForRep Writes a ksh script which turns replication on or off for tables ------------------------------------------------------------------------------ Utilities ------------------------------------------------------------------------------ psArchiveFile Appends '.old' to a file name if the file is too big psCase Converts a phrase to upper or lower case psCompute Evaluates a mathematical expression psControlCharacters Shows the invisible control characters in a text file psConvertFileToLowerCase Converts a text file to lower case psConvertFileToUpperCase Converts a text file to upper case psConvertToWebText Converts a text file to HTML psCopyFile Copies a file psDecode Decodes a base-64 text string into a bit pattern psDeleteFiles Deletes certain files recursively under a directory psDemangle Demangles a C++ symbol psDoesExpressionMatch Shows whether or not text matches a database LIKE expression psDoesProcessExist Displays whether a process exists or not psDocumentAPI Generates web pages of help for PS APIs psDocumentPrograms Generates web pages of help for PS programs psGetComputerName Gets the computer name given an IP address or network address psGetIPAddress Gets the network and IP address given a computer name psHash Displays signed and unsigned hash values for text psHelp Displays a list of PS programs and PS environment variables psIgpayAtinlay Converts your text file into Pig Latin psListFiles Lists the unusual attributes of files in a directory psListParameters Lists the parameters in a parameters database psLoadMessages Writes a messages catalog file given a text file psLoadParameters Writes a parameters database given a text file psMakeMessagesFunction Makes a C++ function which gets PS messages from memory psPortCode Copy a directory structure to another while replacing strings psPrependNotice Prepends a copyright notice to source files psRemoveWhiteSpaces Removes leading and trailing white spaces from records in a text file psRenameFile Renames a file psReplaceStrings Replaces old strings with new strings in a text file psRotateLog Receives data on standard input and writes it to a rotating log file psShowConfigValue Looks up the value for a section and key in a Windows-style .ini file psShowArguments Displays command line arguments as a DBPowerSuite program sees them psShowDirectorySizes Displays the total sizes of directories psShowDiskPerformance Exercises disk reads and writes and displays performance statistics psShowErrno Displays the meaning of an operating system 'errno' code psShowLocale Displays your environment's international locale psShowMessage Displays the text associated with a DBPowerSuite return code psShowParameter Displays the value of a DBPowerSuite parameter psShowPerformance Exercises CPU and memory and displays performance statistics psShowTime Converts system clock times to and from Gregorian times psSleep Sleeps for N seconds psTestGetIPAddresses Tests the ability to get network addresses from computer names psTestLibrary Tests whether a dynamic library will link without other libraries psTranslateIP Translates IP addresses to and from network addresses psUPing Tests if remote TCP/IP servers are up and listening for connections psWhatVersion Displays the DBPowerSuite version number ------------------------------------------------------------------------------ Environment Variables ------------------------------------------------------------------------------ ODBC: DBPowerSuite will use these defaults to login to an ODBC Data Source (a DSN): ------------------------------------------------------------------------------ Data Source Name SybaseFirstsun Server Username ceccareb Password ceccareb Attributes SID=GOLDSUN The ODBC defaults come from these ODBC Environment Variables: ODBC /disk2/ceccareb/PS/os/Solaris/odbc/SparcSol8 ODBCINI /disk2/ceccareb/PS/os/Solaris/odbc/SparcSol8/odbc.ini ODBCSYSINI /disk2/ceccareb/PS/os/Solaris/odbc/SparcSol8 ODBC_DSN SybaseFirstsun ODBC_SERVER ODBC_USER ceccareb ODBC_PASS ceccareb ODBC_ATTR SID=GOLDSUN ODBC_DATABASE Java Environment Variables: ------------------------------------------------------------------------------ JAVA_HOME JDBC_HOME CLASSPATH Sybase: DBPowerSuite will use these defaults to login to a Sybase server: ------------------------------------------------------------------------------ Server firstsun15 Username sa Password sybase Database master The Sybase defaults come from the Environment Variables: DSQUERY firstsun15 SYB_USER sa SYB_PASS sybase SYB_DATABASE master If DBPowerSuite does not find any of the above variables, DBPowerSuite looks next at: SYB_USERNAME ceccareb SYB_PASSWORD ceccareb DBNAME Some other Sybase environment variables: SYBASE /disk2/ceccareb/PS/os/Solaris/sybaseclient/SparcSol8/r15 SYB_OC_VERSION 15001 SYB_OS_VERSION 15001 SYB_OC_VERSION, SYB_OS_VERSION represent the Sybase communication behavior for DBPowerSuite open client and open servers apps respectively. The default value is the first one in the list below. You can change SYB_OC_VERSION and SYB_OS_VERSION to: Sybase 15.0: 15001 Sybase 12.5: 12501 Sybase 12.0: 1101 Sybase 11.x: 1101 Sybase 10.x: 113 SYB_BLK_VERSION represents the bulk copy communication behavior for DBPowerSuite bulk copy clients. The default is the first one in the list below. You can change SYB_BLK_VERSION to: Sybase Bulk Copy 15.0: 15001 Sybase Bulk Copy 12.5: 12501 Sybase Bulk Copy 12.0: 113 Sybase Bulk Copy 11.x: 113 Sybase Bulk Copy 10.x: 113 Oracle Environment Variables ------------------------------------------------------------------------------ ORACLE_SID GOLDSUN ORACLE_HOME /disk2/oracle/Ora92 ORACLE_TERM vt100 PostgreSQL Environment Variables ------------------------------------------------------------------------------ POSTGRES /disk2/ceccareb/psql PGDATA PGLIB PGHOST PGPORT PGDATABASE PGUSER ceccareb PGPASSWORD Other DBPowerSuite Environment Variables ------------------------------------------------------------------------------ DBPOWERSUITE /disk2/ceccareb/PS PS_PACK /disk2/ceccareb/PS/package PS_DB /disk2/ceccareb/PS/dbaccess PS_UTIL /disk2/ceccareb/PS/utilities PS_OS /disk2/ceccareb/PS/os PS_SYSMSG_FILE /disk2/ceccareb/PS/utilities/parameters/messages.cat PS_SYSPARAM_FILE /disk2/ceccareb/PS/utilities/parameters/parameters PS_LETTERLINES PS_PLATFORM Solaris PS_MAKE_OPT 4 PS_TRACE_MASK Operating System Path Variables: ------------------------------------------------------------------------------ PATH /disk2/ceccareb/PS/os/Solaris/sybaseclient/SparcSol8/r15/OCS-15_0/bin /disk2/oracle/Ora92/bin . /disk2/ceccareb/psql/bin /opt/CC5.3/SUNWspro/bin ./usr/java/bin /usr/bin /usr/sbin /usr/ucb /usr/ccs/bin /usr/local/bin /usr/openwin/bin /disk2/ceccareb/ET/package/os_scripts/unix /disk2/ceccareb/ET/xmanager/bin /disk2/ceccareb/ET/xmanager/os_scripts/unix /disk2/ceccareb/PS/license/bin /disk2/ceccareb/PS/package/os_scripts/unix /disk2/ceccareb/PS/dbaccess/bin /disk2/ceccareb/PS/utilities/bin /disk2/ceccareb/PS/utilities/os_scripts/unix /disk2/ceccareb/PS/os/bin LD_LIBRARY_PATH /disk2/ceccareb/PS/license/lib /disk2/ceccareb/PS/dbaccess/lib /disk2/ceccareb/PS/utilities/lib /disk2/ceccareb/PS/os/lib /disk2/ceccareb/PS/os/Solaris/odbc/SparcSol8/lib /disk2/ceccareb/PS/os/Solaris/sybaseclient/SparcSol8/r15/OCS-15_0/lib /disk2/ceccareb/PS/os/Solaris/sybaseclient/SparcSol8/r15/OCS-15_0/lib3p /disk2/ceccareb/ET/xmanager/lib /disk2/oracle/Ora92/lib /opt/CC5.3/SUNWspro/lib /disk2/ceccareb/psql/lib /usr/lib /usr/local/lib /usr/ucblib DYLD_LIBRARY_PATH ######################################################### # # # ODBC Programs # # # ######################################################### --------------------------------------------------------- - - psOLoader - - --------------------------------------------------------- psOLoader: This program loads your text data file into a table at an ODBC data source. As it loads, this program checks each field in each record of your file to see whether it is compatibile with its corresponding column data type. It checks *before* it copies the data to the ODBC data source. If there is a problem with any field, this program tells you the record number where the problem occurred, and then immediately ends with a *non-zero* exit code. If this program does not detect any problems either by its own algorithms or by messages returned from the ODBC data source, then this program ends with a 0 (successful) exit code. A single error will cause psOLoader to exit with a non-zero code. For data type errors, psOLoader always tells you the offending line number. If you are getting duplicate row errors, psOLoader can only give you the offending range of records. The range of records matches the size of the batch (-b). If you make your batch size 1, then you will know the exact number of the duplicate record. Syntax: psOLoader -Sdsn -Vserver -Uusername -Ppassword -Dcatalog -Wowner -Ntable -Binput_text_file -Hheader_records -bbatch_size -Zcolumn_delimiter_string -Yrow_terminator_string -Lleading_character_to_remove -Ttrailing_character_to_remove -cmax_char_len -mmax_record_len -xmax_image_or_text_len -Flog -Mmax_log_size -Ee-mail_address -Ooptions Arguments: -S ODBC Data Source -V ODBC Server Name -U ODBC User Name -P ODBC User's Password -A ODBC Other Attributes -D ODBC Catalog (Database) -W Table belongs to this 'schema' -N Table having pattern 'name' -C Column to convert from binary to text -B Name of the input load file [standard input] -H Number of header records to ignore [0] -b Number of rows in a single transaction [1] -Z Column delimiter string [|] -Y Row terminator string [operating system default record terminator] -L Leading character to remove -T Trailing character to remove -c Minimum char/varchar column size -m Maximum record size [104096] -x Maximum blob size [100000] -t Convert boolean true values to this value -f Convert boolean false values to this value -F Log file [standard output] -M Maximum Log Size [1000000] -E E-mail address to report errors -O Options: m Use Data Direct ODBC drivers l Convert incoming table name to lower case u Convert incoming table name to upper case n If table does NOT exist, ignore the problem and then exit successfully t Truncate table before loading f Use conversion functions {fn convert(?, SQL_BIGINT)} etc. when binding p Bind parameters but do NOT use prepared statements a Use ad-hoc SQL. Do not bind parameters x REMOVE any 0x prefix from the column hex string values o Convert column hex strings to text w/octal strings (binary column is the target) s Convert column hex strings to text (binary column is the target) c Convert -C column hex strings to text w/octal strings (char column in target) d Convert -C column hex strings to text (char column is target) h Convert ADE 128-255 bytes in strings to octal strings (char column is target) q Enclose binary column values in single quotes w Remove leading and trailing white spaces from fields b Convert empty boolean fields to false A Bind all character columns as ascii U Bind all character columns as unicode 1 Sort column names (case sensitive) 2 Sort column names (case insensitive) psOLoader looks for the environment variables: ODBC_DSN When -S is not specified. ODBC_SERVER When -V is not specified. ODBC_USER When -U is not specified. ODBC_PASS When -P is not specified. ODBC_ATTR When -A is not specified. PS_SYSMSG_FILE Name of the tool's messages file. PS_SYSPARAM_FILE Name of the system parameter file Notes: This program is incredibly powerful, but you have to know what you are doing before running it. You have to be aware of your target's ODBC driver limitations in order to choose the right options. Be assured: this program does supply the all the options your need to load a text file of delimited data into an ODBC table. You choose between two routes. Without specifying any -O options, i.e., the default behavior, this program attempts to upload your text file using the ODBC driver's text-to-database-data-type conversion functions. BY DEFAULT, I 'bind' your data and I 'prepare' SQL insert statements. This is the best route you can go. It is the most efficient. However, your ODBC driver may not support prepared statements. Therefore, you must use the -Op option which turns off prepared statements. Sometimes your ODBC driver will not support data type conversions. Even though text-to-database-type conversions are required by the ODBC standard, some companies will not bother implementing them. For example, Data Direct's DBase Foxpro driver does not support the conversion functions. For those ODBC drivers that lack this functionality, I provide you with the -Oa option. -Oa tells me to build full-text ad-hoc SQL statements on the fly. This ad-hoc SQL statements are very inefficient, and now you need to be aware of your ODBC driver's requirements regarding binary column data. Regardless of driver, all binary-column data must be in hexidecimal. But whether that data must be prefixed with an '0x' or whether the data must be enclosed in quotes, depends entirely on the driver. ODBC drivers are not consistent. For example, the Data Direct Sybase ODBC driver requires an 0x with no quotes. But the Data Direct DBase driver requires that the 0x be absent and the data enclosed in quotes. 2009.06.18 21:11:39 psOLoader ER00012 You requested the program's help function. --------------------------------------------------------- - - psOCopyTableData - - --------------------------------------------------------- psOCopyTableData: This program writes a SQL script, which when run using psOSQL, dumps all table data to text files. This programs also creates a operating system script which when run, runs psOLoader for each table. Syntax: psOCopyTableData -Sdsn -Vserver -Uusername -Ppassword -Dcatalog -Wowner -Ntable -Eexc_tables_pattern -Ilist_of_tables_file -Zcolumn_delimiter_string -Yrow_terminator_string -Xprefix_for_generated_scripts -Qsplit_dump_file_size -Rargs_for_psOLoader -Mmax_log_size -Foutput_scripts_prefix -Ooptions Arguments: -S ODBC Data Source -V ODBC Server Name -U ODBC User Name -P ODBC User's Password -A ODBC Other Attributes -D ODBC Catalog (Database) -W Table belongs to this 'schema' -N Table having pattern 'name' -E Exclude tables having pattern 'exc' -I Input file containing list of tables names to copy -x Maximum image/text size [10000000] -Z Column delimiter string [|] -Y Row terminator string [operating system default record terminator] -X Output psOSQL scripts prefix [tables] -Q Maximum file size for each table before splitting [0] -R Arguments to pass to psOLoader (enclose in quotes) [] -M Maximum log size. [0] -F Output log file [standard output] -O Options: m Use Data Direct ODBC drivers w Remove all leading and trailing white spaces n Make sure not-null columns will be not null. Turn nulls to zeros or spaces. N For null columns as well, turn the values into zeros or spaces. s Include schema name with table names 1 Sort column names (case sensitive) 2 Sort column names (case insensitive) u Convert object names to upper case in SQL l Convert object names to lower case in SQL o Target database is Oracle y Target database is MySQL psOCopyTableData looks for the environment variables: ODBC_DSN When -S is not specified. ODBC_SERVER When -V is not specified. ODBC_USER When -U is not specified. ODBC_PASS When -P is not specified. ODBC_ATTR When -A is not specified. PS_SYSMSG_FILE Name of the tool's messages file. PS_SYSPARAM_FILE Name of the system parameter file Notes: If you specify a non-zero value for the -M argument, this program write a SQL script which creates one directory for each tables' data, and then creates an OS script which reads the data for a table from the table's own directory. -Q causes psOSQL to split a text dump into separate files when the data exceeds -Q bytes. 2009.06.18 21:11:40 psOCopyTableData ER00012 You requested the program's help function. --------------------------------------------------------- - - psODropIndices - - --------------------------------------------------------- psODropIndices: This program writes a SQL script, which when run using psOSQL, drops indices for tables. Syntax: psODropIndices -Sdsn -Vserver -Uusername -Ppassword -Dcatalog -Wowner -Ntable -Eexc_table_name_pattern -Ilist_of_tables_file -Foutput_script -Ooptions Arguments: -S ODBC Data Source -V ODBC Server Name -U ODBC User Name -P ODBC User's Password -A ODBC Other Attributes -D ODBC Catalog (Database) -W Table belongs to this 'schema' -N Table having pattern 'name' -E Exclude tables with pattern -I Input file containing list of tables names to copy -F Output file [standard output] -O Options: m Use Data Direct ODBC drivers s Include schema name with table.index names psODropIndices looks for the environment variables: ODBC_DSN When -S is not specified. ODBC_SERVER When -V is not specified. ODBC_USER When -U is not specified. ODBC_PASS When -P is not specified. ODBC_ATTR When -A is not specified. PS_SYSMSG_FILE Name of the tool's messages file. PS_SYSPARAM_FILE Name of the system parameter file 2009.06.18 21:11:40 psODropIndices ER00012 You requested the program's help function. --------------------------------------------------------- - - psOHelp - - --------------------------------------------------------- psOHelp: This program lists all the ODBC and Utility programs in DBPowerSuite. It also shows you the current values of important environment variables. Syntax: psOHelp -Flog_file Arguments: -F Output log [standard output] psOHelp looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:11:40 psOHelp ER00012 You requested the program's help function. --------------------------------------------------------- - - psOListIndices - - --------------------------------------------------------- psOListIndices: This program writes a text file listing the names of the indices at an ODBC data source. You can limit the list to those indices belonging to a set of tables (-W, -N, -E and -I). Syntax: psOListIndices -Sdsn -Vserver -Uusername -Ppassword -Dcatalog -Wowner -Ntable -Eexc_table_name_pattern -Ilist_of_tables_file -Foutput_script -Ooptions Arguments: -S ODBC Data Source -V ODBC Server Name -U ODBC User Name -P ODBC User's Password -A ODBC Other Attributes -D ODBC Catalog (Database) -W Table belongs to this 'schema' -N Table having pattern 'name' -E Exclude tables with pattern -I Input file containing list of tables names to copy -F Output file [standard output] -O Options: m Use Data Direct ODBC drivers t Write the index name using table.index format s Write the index name using schema.index format a Write the index name using schema.table.index format psOListIndices looks for the environment variables: ODBC_DSN When -S is not specified. ODBC_SERVER When -V is not specified. ODBC_USER When -U is not specified. ODBC_PASS When -P is not specified. ODBC_ATTR When -A is not specified. PS_SYSMSG_FILE Name of the tool's messages file. PS_SYSPARAM_FILE Name of the system parameter file 2009.06.18 21:11:40 psOListIndices ER00012 You requested the program's help function. --------------------------------------------------------- - - psOListProcedures - - --------------------------------------------------------- psOListProcedures: This program writes text file containing a list of procedure and function names contained in your specified schema. You can use the output of this program as input to programs like psGWriteSQL. Syntax: psOListProcedures -Sdsn -Vserver -Uusername -Ppassword -Dcatalog -Wowner -Nproc -Eexc -Foutput_file -Ooptions Arguments: -S ODBC Data Source -V ODBC Server Name -U ODBC User Name -P ODBC User's Password -A ODBC Other Attributes -D ODBC Catalog (Database) -W Procedure belongs to this 'schema' -N Procedure having pattern 'name' -E Exclude procedures having pattern 'exc' -F Output file [standard output] -O Options: m Use Data Direct ODBC drivers s Write schema name 1 Sort table names (case sensitive) 2 Sort table names (case insensitive) psOListProcedures looks for the environment variables: ODBC_DSN When -S is not specified. ODBC_SERVER When -V is not specified. ODBC_USER When -U is not specified. ODBC_PASS When -P is not specified. ODBC_ATTR When -A is not specified. PS_SYSMSG_FILE Name of the tool's messages file. PS_SYSPARAM_FILE Name of the system parameter file 2009.06.18 21:11:41 psOListProcedures ER00012 You requested the program's help function. --------------------------------------------------------- - - psOListTables - - --------------------------------------------------------- psOListTables: This program writes text file containing a list of table names contained in your specified schema. You can use the output of this program as input to programs like psGTruncateTables and psGUpdateStatistics. Syntax: psOListTables -Sdsn -Vserver -Uusername -Ppassword -Dcatalog -Wowner -Ntable -Eexc -Foutput_file -Ooptions Arguments: -S ODBC Data Source -V ODBC Server Name -U ODBC User Name -P ODBC User's Password -A ODBC Other Attributes -D ODBC Catalog (Database) -W Table belongs to this 'schema' -N Table having pattern 'name' -E Exclude tables having pattern 'exc' -F Output file [standard output] -O Options: m Use Data Direct ODBC drivers s Write schema name 1 Sort table names (case sensitive) 2 Sort table names (case insensitive) psOListTables looks for the environment variables: ODBC_DSN When -S is not specified. ODBC_SERVER When -V is not specified. ODBC_USER When -U is not specified. ODBC_PASS When -P is not specified. ODBC_ATTR When -A is not specified. PS_SYSMSG_FILE Name of the tool's messages file. PS_SYSPARAM_FILE Name of the system parameter file 2009.06.18 21:11:41 psOListTables ER00012 You requested the program's help function. --------------------------------------------------------- - - psOListViews - - --------------------------------------------------------- psOListViews: This program writes text file containing a list of view names contained in your specified schema. Syntax: psOListViews -Sdsn -Vserver -Uusername -Ppassword -Dcatalog -Wowner -Nview -Eexc -Foutput_file -Ooptions Arguments: -S ODBC Data Source -V ODBC Server Name -U ODBC User Name -P ODBC User's Password -A ODBC Other Attributes -D ODBC Catalog (Database) -W Views belongs to this 'schema' -N Views having pattern 'name' -E Exclude views having pattern 'exc' -F Output file [standard output] -O Options: m Use Data Direct ODBC drivers s Write schema name 1 Sort view names (case sensitive) 2 Sort view names (case insensitive) psOListViews looks for the environment variables: ODBC_DSN When -S is not specified. ODBC_SERVER When -V is not specified. ODBC_USER When -U is not specified. ODBC_PASS When -P is not specified. ODBC_ATTR When -A is not specified. PS_SYSMSG_FILE Name of the tool's messages file. PS_SYSPARAM_FILE Name of the system parameter file 2009.06.18 21:11:41 psOListViews ER00012 You requested the program's help function. --------------------------------------------------------- - - psOSQL - - --------------------------------------------------------- psOSQL: This program sends ODBC SQL language commands to an ODBC data source. You can run this program using a command string, a SQL input file, or interactively. To run the program interactively, type psOSQL -Op or . . . psOSQL -Omp when you are using Data Direct closed drivers. The 'p' stands for prompt mode. If any of your SQL commands fails during the course of your session, then psOSQL will exit with a non-zero exit code. If every command executes successfully, then psOSQL will exit with 0. Syntax: psOSQL -Sdata_source -Vserver -Uusername -Ppassword -Aattributes -Tquery_time_out -tlogin_time_out -ppacket_size -Ccommand -Isql_file -Bbatch_terminator -bmax_batch_len -mmax_rcd_len -xmax_image_len -Zbypass_characters -Fresults_file -Rmax_results_file_size -Xcolumn_delimiter_string -Yrow_terminator_string -Lerror_log_file -Mmax_error_log_size -Ee-mail_address -Kkey_word -kkey_word_value -Ooptions Arguments: -S ODBC Data Source -V ODBC Data Source's Server -U ODBC Data Source Userame -P ODBC Data Source Password -A ODBC Other Connection Attributes -T Maximum Time-out waiting for Results from the Data Source [14400 seconds] -t Maximum Time-out waiting for a Connection from the Data Source [10 seconds] -p Network packet size (bytes) [ defers to ODBC driver] -C Input single SQL command -I Input SQL command file [standard input] -B Batch terminator [; at the end of the line] -b Maximum batch length [200000] -m Maximum input record length [100000] -x Maximum output image/text length [20480] -Z Bypass characters [#] -F Output results file [standard output] -N Null values are displayed with this text [] -X Column delimiter string for output results file [|] -Y Row terminator string for output results file [standard operating system record terminator] -R Maximum starting size of the existing results file [0 bytes] -Q The maximum split size of the results file [0 bytes] -L Output messages log [output results file] -M Maximum starting size of the existing messages log [0 bytes] -E E-mail address when error -K (upper case K) Keyword or keyword list -k (lower case k) Keyword value or keyword value list -O Options: p Interactive mode P Interactive mode with connection prompts m Use Data Direct closed drivers u Set login and result time out properties n Send program name to the data source q Allow quoted identifiers x Look for batch terminator at the beginning of the line r Discard all results c Do not comment results file i Do not echo your input as comments in the results file t Prefix comments with a time stamp b Remove leading and trailing blanks from results' columns s Put results into vertical columns; do not use the | delimiter e Continue to next batch despite when I get an error from the data source E Always exit with success (0) even when I get an error from the data source a Do not put abbreviated non-error info messages in the error log l Put full non-error info messages into the error log psOSQL looks for the environment variables: ODBC_DSN When -S is not specified. ODBC_SERVER When -V is not specified. ODBC_USER When -U is not specified. ODBC_PASS When -P is not specified. ODBC_ATTR When -A is not specified. PS_SYSMSG_FILE Name of the tool's messages file. PS_SYSPARAM_FILE Name of the system parameter file Notes_to_Oracle_Users: ------------------------------------------------------------------------------ If you are going to input PL/SQL, you will have to change your batch terminator to '/' and tell psOSQL to look for this '/' at the beginning of the line. To tell psOSQL to behave like this, run psOSQL prompt then type . . . batch / start Once done, you can append ';' at the end of lines within your PL/SQL block without psOSQL sending these intermediate lines to Oracle. The slash '/', instead, sends your SQL to Oracle. This psOSQL provision is to get around the Oracle architectural mistake of not having a single SQL language. In one Oracle SQL language, the ';' sends your command to the server. In the other SQL language (PL/SQL), the semicolon is a construct that concludes a line, but does not send your line to the server. DBPowerSuite does not attempt to fix Oracle's schizophrenia. Running_SQL_Files_&_Changing_Connections_Within_a_Session: ------------------------------------------------------------------------------ You can run SQL files by entering run your.sql where 'your.sql' is the name of your SQL file. Your file itself can contain run commands psSQL will be able to run nested SQL files up to 10 levels deep. You can connect to a different ODBC data sources from within a session: connect -Sdsn -Vserver -Uuser -Ppass -Tquery_to -tlogin_to -Olaun The arguments and options of the internal connect command mean the same thing as the arguments and options of psOSQL's command line. You can only be connected to one data source at a time. During a session, there is no limit to the number of connections you can make. There are about 20 other psOSQL internal commands. Type 'help' at the prompt to see them all. Exit_Code: ------------------------------------------------------------------------------ 1. psOSQL terminates with a meaningful exit code. If the exit code is 0 then psOSQL finished without detecting any errors from the data source. If the exit code is not 0, then the data source did not like at least one of your commands. 2. You can get the text associated with the exit code by running psShowMessage: psShowMessage -Ccode 3. psOSQL does understand the difference between errors verses informational messages. psOSQL does not count informational messages as an error. Stopping_psOSQL_in_the_Middle_of_Your_File: ------------------------------------------------------------------------------ 1. By default, psOSQL stops processing your file when it either 1) comes to the end of your file or 2) comes to the end of a batch--the data source having thown an error during the execution of the batch. (Batches are separated by the ';' in ODBC or by your chosen batch terminator.) psOSQL does not send commands after it detects the bad batch. 2. You can change this 'stop on error' behavior. By putting -Oe on the command line, psOSQL will continue to the next batch in spite of prior errors. Yet still, when psOSQL finally ends, it will still end with a non-zero exit code if it detected errors. 3. You can conditionally exit your file with an error by coding a data source 'raiserror' command in your SQL file. In Sybase or Microsoft a 'raiserror' commands force the data source to throw an error. 4. You can unconditionally quit your file in the middle and have psOSQL end with a *successful* exit code. You do this with a specific SQL 'print' command. In the case of Sybase or Microsoft SQL Server: print 'exit psOSQL' And at the end of the batch which executes this command, and assuming no prior errors, psOSQL will exit successfully. Interactive_Mode: ------------------------------------------------------------------------------ 1. To engage interactive mode, use the -Op option. To see the list of interactive commands native to psOSQL, type 'help' at the psOSQL prompt. By default, psOSQL executes in non-interactive mode. You will not see the psOSQL prompt. Also, certain other psOSQL-native commands will not be available to you. For example, you can not use the native commands history, recall or clean. Non-Interactive_Modes: ------------------------------------------------------------------------------ 1. You can send a single command to the data source by coding it right on the command line. To do this, use the -C option and enclose your command in quotes. 2. You can send a file of several commands and batches to the data source. Use the -I option along with your file name. 3. If you do not use -C or -I, then psOSQL looks for command input from UNIX standard input. In this fashion, you can use here-files from within shell scripts or pipe input into psOSQL from other programs. Bypass_Characters: ------------------------------------------------------------------------------ 1. psOSQL will skip over lines in your SQL file which start with any of the bypass characters. psOSQL will not send these lines to the ODBC data source. These lines will not be a part of the batch. 2. By default, psOSQL will skip any lines beginning with a #. If you do not want psOSQL to skip any lines, specify -Znobypass Data-Direct-ODBC,_Timeout_Properties,_and_Sending_the_Program_Name: ------------------------------------------------------------------------------ 1. Specify the -Om option only when you are using DataDirect Technology's closed drivers. -Om tells psOSQL to look into DBPowerSuite's system parameter file for the closed driver license name and password. 2. Specify the -Ou option if you want to set the login and result time out properties for the data source. This means that the ODBC driver will timeout with an error if results do not come back from the data source within -T seconds. Also, the ODBC driver will timeout with an error if you cannot login to the data source within three seconds. I made setting the timeout properties an option because some data source and/or some ODBC drivers do not support timeout properties. For instance, Oracle does not. If you try setting timeout properties on an Oracle data source, you will get an error. 3. Specify the -On if you want to send the program name, that is, 'psOSQL', as a connection property to the data source. DBAs like it when you do this because they can then see that your instance of psOSQL is connected to their database server. -On is optional because some data sources do not support sending the program name to the data source. Keyword_Substitution: ------------------------------------------------------------------------------ 1. By using the command line arguments -Kold_string and -knew_string, you can make psOSQL replace every occurance of old_string in your input commands with new_string. In this way, you can avoid using 'sed' prior to running a SQL script through psOSQL. Do not worry, I will not modify your original input file in any way. 2. Actually, old_string can be more than a single key word. Consider this on your command line: -K '_STATE _COUNTRY' -k 'NC USA' psOSQL will replace every occurance of _STATE with NC. psOSQL will also replace every occurance of _COUNTRY with USA. You can place up to 10 key words next to -K or -k. 3. There are 3 reserved built-in keywords: __DBNAME__, __DATABASE__ and __USERNAME__. Everytime psOSQL finds __DBNAME__ or __DATABASE__ in your input, psOSQL replaces it with the -D psOSQL command line argument. Everytime psOSQL finds __USERNAME__ in your input, psOSQL replaces it with the -U command line argument. (There are 2 underscores before and after these special keywords.) Results_File: ------------------------------------------------------------------------------ 1. All data sources send psOSQL two types of data. The two types of data are 1. Results 2. Messages By default, psOSQL sends both kinds of data to the results file. You specify a results file with the -F command line argument. If you not specify the -F your results go to standard output. 2. At this point, let us discuss the differences between 'results' and 'messages' data. Results data is the rows of data produced from select and fetch commands, the return codes from stored procedure and the rows-affected counts from select/update/delete/insert SQL commands. Messages data, on the other hand, are information created by your SQL 'print' commands and 'raiserror' commands. Also, data sources may create Messages type data all the time. Everytime a Sybase Server changes database it sends Messages type data. Everytime a Sybase Server throws an error, it sends Messages type data. 3. With psOSQL, you can split the results data from the messages data. If you specify the -L command line argument, you direct messages to go to a different destination than the results. If you do not specify -L, the messages will go to the same destination as your results. The advantage of having your results and your messages go to the same destination is that you will know what messages go with what results. You will be able to debug your input commands better. The disadvantage is is that message type data is difficult to parse from the surrounding results. I advise that you only separate results from messages when you are certain that the data source will process all your commands successfully and your results are input to another program. 4. You can tell psOSQL to append new results to an existing results file by using the -R option. With the -R, you can specify the maximum size of existing results file. If the existing results file is over this size, psOSQL appends a '.old' to the existing file, and then creates a new one for new results to be written into. If the existing results file is less than the -R maximum size, then psOSQL just appends its new results to the existing file. 5. You can tell psOSQL to split the results file after it gets too large. If you set the -Q parameter to 100000, then when the file gets to be larger than 100000 bytes, psOSQL will close the file, and start a new file. psOSQL appends a sequential number to the file name. 6. Using the 'save' psOSQL native command, you can create many results files within a single psOSQL session. To see the details of 'save', run psOSQL interactively and type 'help'. 7. Discarding results. You can tell psOSQL to execute your commands but throw away all results. No results file will be created. Use the -Or to accomplish this. Comments_in_your_Results_File: ------------------------------------------------------------------------------ 1. By default, psOSQL will put comments in your results file. Comments take only 2 forms. They either start with the # or they are totally blank lines. 2. If you are going to use the results as input into another program, then you most likely do not want comments in your file. To remove comments, use the -Oc option. If you don't use -Oc, then your program must filter out the comments by using the criteria described in # 1. 3. There may be occasions when you want to time how long your commands take. If you use -Ot, then psOSQL will prefix a time stamp to every comment it makes. 4. By using nocomment, comment or timecomment psOSQL native commands, you can change the way psOSQL comments your results during a session. Column_Delimiters_in_the_Results_File: ------------------------------------------------------------------------------ 1. By default, psOSQL delimits every column with the | character in non-interactive mode. 2. By default, psOSQL mimics isql in the way it writes results in interactive mode. Columns are separated by varying number of spaces in order to make nice vertical columns. 3. To change the default, you can use the -X command line option. Or you can use the psOSQL native command 'delimit'. If you specify a number for the delimiting character, I assume your number is the ASCII decimal equivalent of the desired character. If you specify a letter to begin with, then I use that letter. 4. For BCP operations, | is the definitive delimiting character. Also, it the perfect delimiter if you are programming with DBPowerSuite' UFile class. 5. If you specify -Os on the command line, you get isql-like result output. 'delimit -1' also will get you isql-like output. 6. If your results will be read into Excel, use -X9 as the delimiter. 9 is the ASCII decimal equivalent of a tab. Excel will read such tab-delimited results files directly. Each column will fall into its own column of Excel cells. Messages_Log_File: ------------------------------------------------------------------------------ 1. By default, data source Messages type data will go to the same destination as the results data. Refer to the 'Results File' section. If you declare a -Llog command line argument, then all the data source's messages will go to file 'log' instead. 2. You can tell psOSQL to append new messages to an existing log file by using the -M option. With the -M, you can specify the maximum size of existing messages file. If the existing messages file is over this size, psOSQL appends a '.old' to the existing file, and then creates a new one for new messages to be written into. If the existing messages file is less than the -M maximum size, then psOSQL just appends its new messages to the existing file. E-mail. You can tell psOSQL to e-mail all the error messages to anybody on the internet. If you specify the -E command line argument *and* there are errors (not just information messages, but actual errors), then psOSQL will e-mail all the errors when psOSQL exits. 2009.06.18 21:11:42 psOSQL ER00012 You requested the program's help function. --------------------------------------------------------- - - psOPing - - --------------------------------------------------------- psOPing: This program tests whether you can coonect to an ODBC data source. Syntax: psOPing -Sdsn -Vserver -Uusername -Ppassword -Aother_connection_attributes -Flog_file Arguments: -S ODBC Data Source Name -V ODBC Data Source's Server -U ODBC User Name -P ODBC User Password -A ODBC Other Connection Attributes -F Log file -O Options: m You are using DataDirect closed drivers x Set timeout properties p Identify the program name to the database psOPing looks for the environment variables: ODBC_DSN When -S is not specified. ODBC_SERVER When -V is not specified. ODBC_USER When -U is not specified. ODBC_PASS When -P is not specified. ODBC_ATTR When -A is not specified. PS_SYSMSG_FILE Name of the tool's messages file. PS_SYSPARAM_FILE Name of the system parameter file 2009.06.18 21:11:42 psOPing ER00012 You requested the program's help function. --------------------------------------------------------- - - psOTestReconnects - - --------------------------------------------------------- psOTestReconnects: This program tests how well the ODBC driver can recover from a down database. This program will create a single test table in your database called 'PSTEST'. It will attempt to insert, select and delete rows from this table. To test the ODBC drivers ability to recover, log into your database using the database's own SQL-session program, and kill this programs connection. Then look at this program's log to see how well it reconnected. Syntax: psOTestReconnects -Sdsn -Vserver -Uusername -Ppassword -Aother_attributes -Itest_table.sql -Xnumber_of_loops -Rnumber_of_test_rows -Tsleep_time -Flog_file -Ooptions Arguments: -S ODBC Data Source Name -V ODBC Server Name -U ODBC User Name -P ODBC User Password -A ODBC Other Connection Attributes -I Test Table Creation SQL Script Name [See below for default.] -X Number of times to execute the algorithm on each thread [10] -R Number of rows to insert [1000] -T Sleep time between transactions in seconds [0] -F Log file -O Options m Use Merant ODBC drivers a Send application name to data source t Use login and results time outs p Prepare statements psOTestReconnects looks for the environment variables: ODBC_DSN When -S is not specified. ODBC_SERVER When -V is not specified. ODBC_USER When -U is not specified. ODBC_PASS When -P is not specified. ODBC_ATTR When -A is not specified. PS_SYSMSG_FILE Name of the tool's messages file. PS_SYSPARAM_FILE Name of the system parameter file Notes: I will default the Test Table Creation Script to: /disk2/ceccareb/PS/dbaccess/sql/oracle/test_table.sql 2009.06.18 21:11:42 psOTestReconnects ER00012 You requested the program's help function. --------------------------------------------------------- - - psOTestThreads - - --------------------------------------------------------- psOTestThreads: psOTestThreads serves two purposes. 1. Tests the thread-safety of an ODBC driver. This program determines whether an ODBC driver and its associated dependent libraries are thread safe. If the driver system is not thread-safe, this program usually core dumps. Other times, this program may log some sort of unusual error. Just remember: If the driver is thread-safe, and the ODBC driver supports the ODBC functions this program calls (which it should), this program runs 2. Times the speed of the ODBC driver. This program computes transactions rates for the test*.sql scripts. See below. Syntax: psOTestThreads -Sdsn -Vserver -Uusername -Ppassword -Aother_attributes -ppacket_size -Itest_table.sql -Tnumber_of_threads -Xnumber_of_loops -Rnumber_of_test_rows -Ynumber_of_rows_per_select -Flog_file -Ooptions Arguments: -S ODBC Data Source Name -V ODBC Server Name -U ODBC User Name -P ODBC User Password -A ODBC Other Connection Attributes -p Network packet size in bytes [defers to ODBC driver] -I Test Table Creation SQL Scripts Directory [See below for default.] -T Number of connection threads to the ODBC data source [3] -X Number of times to execute the algorithm on each thread [3] -R Number of rows to insert [100] -Y Number of rows to get for each select [10] -F Log file -O Options m Use Data Direct ODBC drivers a Send application name to data source t Use login and results time outs p Prepare statements s Stored procedures n Do NOT drop stored procedures i Create indexes r Skip reading the rows from the database u Update statistics after inserting rows psOTestThreads looks for the environment variables: ODBC_DSN When -S is not specified. ODBC_SERVER When -V is not specified. ODBC_USER When -U is not specified. ODBC_PASS When -P is not specified. ODBC_ATTR When -A is not specified. PS_SYSMSG_FILE Name of the tool's messages file. PS_SYSPARAM_FILE Name of the system parameter file Required_SQL_Scripts: --------------------- This programs depends on previously written SQL scripts which creates the test tables and stored procedures. The program looks for the scripts in the Test Table Creation Script Directory which defaults to . . . /disk2/ceccareb/PS/dbaccess/sql/oracle This program always use test_table.sql. If you specify the stored procedures option, then the program also uses: test_package.sql test_insert.sql test_select.sql test_update.sql This program only runs test_package.sql when the file exists. The package script is necessary only for Oracle. If you specifiy the -Oi option, this program creates an index on each test table. The test_index.sql scripts create the index on the index_area tablespace. The only reason to create indexes would be if you want to test the disk speed of your tablespace configuration. Number_of_Rows_To_Get_For_Each_Select: -------------------------------------- The number of selects (per loop) this program runs on a table equals the number of rows in a table. You can tell this program to fetch more than 1 row per select statement by setting the -Y argument. Why does this program allow you to do this? Because different ODBC drivers incur different overheads given a data stream. I/O is an expensive operation when comes to elapsed time. Therefore, this program allows you to increase the amount of I/O significantly by letting you bump up the amount of data coming back from the database. Therefore, not only are you are testing how fast the database executes a query, but also you are testing the compactness of the data stream protocol. Source_Code: ------------ Talus Software has given you the source code for this program. Look in the dbaccess/demo directory for psOTestThreads. We provide the source for these reasons: 1. Nobody trusts tps times. So seeing is believing. By giving you the source code, you will see that the program does nothing special between ODBC data sources. You will see that the playing field is level. By running this program, you will produce benchmarks for yourself. You, like us, will find that Oracle is the worst database on the planet. Even dBase performs better than Oracle. You will also discover that Sybase will beat all the competition. Therefore, I provide the source code so that you will discover the truth for yourself. By the way, by giving you the the source code, you can literally change the test SQL queries to do whatever you want . . . which also means . . . 2. You will see how to use the PS API to write your own PS-based applications. This program is a great demonstration of the PS API. It uses the PS API to handle threads, to execute SQL, and to manage ODBC connections using the very useful PS's ODBC Multithreaded Connection Pool Resource Manager. 2009.06.18 21:11:43 psOTestThreads ER00012 You requested the program's help function. ######################################################### # # # Generic Database Programs # # # ######################################################### --------------------------------------------------------- - - psGAddCheckConstraints - - --------------------------------------------------------- psGAddCheckConstraints: This program writes a SQL which when run, adds check constraints to the tables in a database. Though the program reads the Sybase system tables for the constraint information, the program can generate a SQL script for a number of different brands of databases including Oracle. Syntax: psGAddCheckConstraints -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Foutput_sql_script -Ooptions Arguments: -S Source Sybase Server Name -U Source Sybase User Name -P Source Sybase User Password -D Source Sybase Database -W Check tables owned by 'owner' only [-U] -N Check tables having pattern 'name' [!rs%] -F Name of output SQL script [standard output] -O Options: s Generate a Sybase SQL isql script m Generate a Microsoft SQL isql script o Generate an Oracle SQL*Plus script g Generate an ODBC SQL script psGAddCheckConstraints looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:11:43 psGAddCheckConstraints ER00012 You requested the program's help function. --------------------------------------------------------- - - psGAddRIConstraints - - --------------------------------------------------------- psGAddRIConstraints: This program writes a SQL script which adds referential integrity constraints. This programs gets its definitions for a user's schema from the system tables of a Sybase database. Therefore, in order to run this program, you need a up-and-running Sybase ASE server. Once the program retrieves the definitions, the program then generates a SQL script for a Sybase database, a MS SQL Server database, an Oracle database or a generic ODBC data source. It is your choice. You run this program on your untouched production database. You can use the SQL script the program generates later to reestablish the RI constraints if you suspect that someone has removed them in the interim. Syntax: psGAddRIConstraints -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Foutput_SQL_script -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Use tables belonging to 'owner' only -N Use tables having pattern 'name' [!rs%] -F Name of output SQL script [standard output] -O Options: s Generate a Sybase SQL script m Generate a Microsoft SQL script o Generate an Oracle SQL script g Generate an ODBC SQL script psGAddRIConstraints looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:43 psGAddRIConstraints ER00012 You requested the program's help function. --------------------------------------------------------- - - psGChangeTablespaces - - --------------------------------------------------------- psGChangeTablespaces: From a list of tables or indices in an RI map, psGChangeTablespaces creates a a SQL script to change the tablespace of each table or index. the tables. You can generate a list of tables or indexes using psOListTables, psOListIndices, psGenerateRIMap, or by a clever SQL command via psOSQL or psSQL. Syntax: psGChangeTablespaces -Tri_map_file -Xtablespace -Nschema_name -Fnew_sql_file -Ooptions Arguments: -T RI Map File -N Schema Name (optional) -X Target Tablespace -F Output SQL Script [standard output] -O Options s Sybase o Oracle m Microsoft SQL Server i Informix r Postgres y MySQL g ODBC u Convert all names to upper case l Convert all names to lower case x The list of objects are indices, not tables p Stop at the first parsing problem t Turn tracing on psGChangeTablespaces looks for the environment variables: PS_SYSMSG_FILE Name of DBPowerSuite's messages file. 2009.06.18 21:11:44 psGChangeTablespaces ER00012 You requested the program's help function. --------------------------------------------------------- - - psGCheckRIConstraints - - --------------------------------------------------------- psGCheckRIConstraints: This program writes a SQL script to check the referential integrity of user tables in a database. This program asks a Sybase database about its RI constraints, and then can generate a SQL script for a Sybase database, a MS SQL Server database, an Oracle database, an Informix database, or any generic ODBC database. There are many reasons why the referential integrity of a database may have been compromised. For Sybase and MS SQL Server, any bulk copy operation into a table will bypass RI checks. For Oracle, somebody may have disabled the RI constraints using an ALTER TABLE statement. Or the reason may be even simpler: RI constraints that should have been in the database were never put there in the first place, and now you want to make sure referential integrity is present and enforced. Syntax: psGCheckRIConstraints -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Vviolations -FSQL_script -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Use tables belonging to 'owner' only -N Use tables having pattern 'name' [!rs%] -V Stop checking table after this number of violations [50] -F Name of output SQL script [standard output] -O Options: s Generate a Sybase SQL script m Generate a Microsoft SQL script o Generate an Oracle SQL script g Generate an ODBC SQL script r Check normal RI based constraints k Check foreign keys defined by sp_foreignkey j Check keys defined with sp_commonkey psGCheckRIConstraints looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:44 psGCheckRIConstraints ER00012 You requested the program's help function. --------------------------------------------------------- - - psGCheckRules - - --------------------------------------------------------- psGCheckRules: This program checks whether your tables' columns violate their check constraints and rules. If you are concerned about the integrity of your data, this program as well as psGCheckRIConstraints are programs which confirm or deny your suspicions. Bad data can enter your tables in many ways. People can drop the constraints. Bulk copy operations ignore constraints. And Mother Nature herself can send a thunderbolt to corrupt the disk itself. dbcc checks for acts of Mother Nature. psGCheckRIConstraints and psGCheckRules check for evil deeds done by users. Syntax: psGCheckRules -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Vmax_violations_to_display -Ooptions Arguments: -S Source Sybase Server Name -U Source Sybase User Name -P Source Sybase User Password -D Source Sybase Database -W Check tables owned by 'owner' only [-U] -N Check tables having pattern 'name' [!rs%] -V Display up this many violations per table [50] -F Name of output SQL script [standard output] -O Options: s Generate a Sybase SQL isql script m Generate a Microsoft SQL isql script o Generate an Oracle SQL*Plus script g Generate an ODBC SQL script psGCheckRules looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:11:44 psGCheckRules ER00012 You requested the program's help function. --------------------------------------------------------- - - psGCopyTableData - - --------------------------------------------------------- psGCopyTableData: This program copies table data from one database to another. This program assumes that the source and target databases are of different vendors. For example, you can copy table data from Oracle to Sybase, from Sybase to MySQL, from Postgres to Sybase . . . . Any combination is possible. This program writes an export and an import script. The form of an export script defaults to a DBPowerSuite psOSQL ODBC session. You can change the default ODBC form for Microsoft SQL Server and Sybase. For Microsoft SQL server, you can instead create a Windows batch file of bcp queryout commands. For Sybase, you create a DBPowerSuite psSQL Sybase Open-Client session. Therefore, for Microsoft and Sybase, you do not need their ODBC driver to export their data. For the import script, this program generates SQLLoader scripts for Oracle, a Windows batch file of bcp commands for Microsoft SQL Server, and a UNIX shell script of bcp commands for Sybase. For other targets like MySQL and Postgres, this program generates a UNIX ODBC psOLoader script. Syntax: psGCopyTableData -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -sODBCdsn, -vODBCserver, -uODBCuser, -pODBCpass -aother_attributes -Acolumn_delimiter_string -Xrow_terminator_string -Jtarget_dsn -Qtarget_server -Rtarget_user -Ttarget_password -Vtarget_database -Ytarget_owner -Zattr -FKorn_shell_script -Ooptions Arguments: Sybase ASE That Contains Table Definitions: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Use tables belonging to 'owner' only [dbo] -N Use tables having pattern 'name' [!rs%] Originating ODBC Data Source: -s ODBC Data Source Name (or MS SQL Server DB name--Windows batch file) -v ODBC Server Name (or MS SQL Server name) -u ODBC User Name (or MS SQL Server user name) -p ODBC Password (or MS SQL Server password) -a ODBC Other Attributes (or MS SQL Server owner name) Target Data Server: -J Target Data Source (only for ODBC target) -Q Target Server -R Target User Name -T Target Password -V Target Database -Y Target Owner -Z Target Other Attributes (optional only for ODBC target) Delimiters and File Name: -A Column Delimiter String [|] -X Row Terminator String [standard OS record terminator] -F Name of output Korn Shell script [standard output] -O Options: S The originating data source is Sybase M The originating data source is Microsoft SQL Server O The originating data source is Oracle R The originating data source is Posgres Y The originating data source is MySQL G The originating data source is generic ODBC s The target data server is Sybase m The target data server is Microsoft SQL Server o The target data server is Oracle r The target data server is Postgres y The target data server is MySQL g The target data server is generic ODBC w Remove all leading and trailing white spaces n Make sure not-null columns will be not null. Turn nulls to zeros or spaces. N For null columns as well, turn the values into zeros or spaces. u Use Data-Direct ODBC drivers. k For Sybase sources, write a psSQL session instead of a psOSQL ODBC session. b For MS SQL Server sources, write a Windows batch file of bcp queryout commands instead of a Korn/psOSQL script. h For Oracle targets, use hex notation for SQLLoader terminators. 8 For Oracle targets, use old 8 bit hex notation for SQLLoader terminators. d For Oracle targets, use SQLLoader direct path. Conventional path in the default. e For Oracle targets, for SQLLoader direct path, reenable disabled constraints. p For generic ODBC targets, do NOT use prepared statements. In any case, 'bind' with ODBC driver data type conversions . . . or . . . a For generic ODBC targets, use ad-hoc non-binding SQL inserts x For generic ODBC targets, REMOVE any 0x hex prefixes in binary column data q For generic ODBC targets, enclose binary data in quotes psGCopyTableData looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. ODBC_Requirements: ------------------------------------------------------------------------------ This program, psGCopyTableData, does not require ODBC drivers to be present to run. The scripts the program generates require ODBC drivers to be present only when you opted to use the ODBC export or import options. For Sybase and Microsoft SQL Server sources, you do not need ODBC. For Oracle sources, you do. For Sybase, Microsoft SQL Server or Oracle targets, you do not need ODBC. For other databases, like MySQL or Postgres, you do. Remember that the MySQL and Postgres ODBC drivers come with DBPowerSuite. You can buy a full set of production quality ODBC drivers for Sybase, Oracle, and Microsoft which run on Solaris and Linux, from Data Direct Technologies. DBPowerSuite readily works with the Data Direct drivers. These drivers are easy to install. It is a one-step process. Schema_Definition: ------------------------------------------------------------------------------ psGCopyTableData depends on you first defining the schema using Sybase. Sybase must be in the picture. Your source and target databases can be different than Sybase. psGCopyTableData reads the Sybase system tables for the table definitions as well as uses the RI constraints to determine which tables are parent tables and which tables are child tables. psGCopyTableData uses the parent/child relationships to determine which table it loads first. It loads parent tables first. The target schema must match the Sybase schema-definition. The column order must be the same. However, the column order in the source database does not have to be the same. psGCopyTableData rearranges the data from the source tables to match that of the target. Database_Location_Independence: ------------------------------------------------------------------------------ The source and the target database can be on completely separated networks. This program generates scripts. It is the scripts you take to the source or target environment and run. The export script generates table data files. You can zip up those data files and ftp them to the target environment. Then you run the import script at the target location against those data files. DBPowerSuite does not have to be present on the source database's network when the source database is Microsoft SQL Server. If the source database is not Microsoft SQL Server, then you will need DBPowerSuite on the network somewhere. 2009.06.18 21:11:45 psGCopyTableData ER00012 You requested the program's help function. --------------------------------------------------------- - - psGCountRows - - --------------------------------------------------------- psGCountRows: From a list of tables in a segment map, psGCountRows creates a SQL script to count the rows in each table. The tables will be counted in the same order as they appear in the segment map. Syntax: psGCountRows -Tsegment_map_file -Fnew_sql_file -Nscript_name -Ooptions Arguments: -T Segment Map File -F Output SQL Script [standard output] -N Script Name (optional) -O Options s Sybase o Oracle m Microsoft SQL Server g ODBC p Stop at the first parsing problem psGCountRows looks for the environment variables: PS_SYSMSG_FILE Name of DBPowerSuite's messages file. 2009.06.18 21:11:45 psGCountRows ER00012 You requested the program's help function. --------------------------------------------------------- - - psGDeleteRIViolators - - --------------------------------------------------------- psGDeleteRIViolators: This program writes a SQL script which drops all rows that violate referential integrity constraints. This program asks a Sybase database about its RI constraints, and then it generates a SQL script for a Sybase, MS SQL Server, Oracle or any generic ODBC database. There are many reasons why the referential integrity of a database may have been compromised. For Sybase and MS SQL Server, any bulk copy operation into a table will bypass RI checks. For Oracle, somebody may have disabled the RI constraints using an ALTER TABLE statement. Or the reason may be even simpler: RI constraints that should have been in the database were never put there in the first place, and now you want to make sure referential integrity is present and enforced. The problem, is, is that before you can add the RI constraints again, you first have to drop all the orphaned rows. See also programs psGAddRIConstraints and psGCheckRIConstraints. Syntax: psGDeleteRIViolators -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -FSQL_script -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Use tables belonging to 'owner' only -N Use tables having pattern 'name' [!rs%] -F Name of output SQL script [standard output] -O Options: s Generate a Sybase SQL script m Generate a Microsoft SQL script o Generate an Oracle SQL script g Generate an ODBC SQL script r Check normal RI based constraints k Check foreign keys defined by sp_foreignkey j Check keys defined with sp_commonkey c Delete rows from tables in table creation order psGDeleteRIViolators looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:45 psGDeleteRIViolators ER00012 You requested the program's help function. --------------------------------------------------------- - - psGDeleteRuleViolators - - --------------------------------------------------------- psGDeleteRuleViolators: This program writes SQL script dropping rows from tables when they violate their check constraints or rules. Bad data can enter your tables in many ways. People can drop the constraints. Bulk copy operations ignore constraints. And Mother Nature herself can send a thunderbolt to corrupt the disk itself. Before you run this program, you can just check if any constraints are violated by running psGCheckRules. Syntax: psGDeleteRuleViolators -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Ooptions Arguments: -S Source Sybase Server Name -U Source Sybase User Name -P Source Sybase User Password -D Source Sybase Database -W Check tables owned by 'owner' only [-U] -N Check tables having pattern 'name' [!rs%] -F Name of output SQL script [standard output] -O Options: s Generate a Sybase SQL isql script m Generate a Microsoft SQL isql script o Generate an Oracle SQL*Plus script g Generate an ODBC SQL script psGDeleteRuleViolators looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:11:45 psGDeleteRuleViolators ER00012 You requested the program's help function. --------------------------------------------------------- - - psGDropCheckConstraints - - --------------------------------------------------------- psGDropCheckConstraints: From a list of tables in a segment map, psGDropCheckConstraints creates a a SQL script to drop all check constraints on those tables. The SQL will contain dynamical SQL, which in turn, reads the database's system tables for the names of the constraints. Syntax: psGDropCheckConstraints -Tsegment_map_file -Fnew_sql_file -Nschema_name -Ooptions Arguments: -T Segment Map File -F Output SQL Script [standard output] -N Script Name (optional) -O Options s Sybase o Oracle m Microsoft SQL Server i Informix r Postgres y MySQL g ODBC p Stop at the first parsing problem psGDropCheckConstraints looks for the environment variables: PS_SYSMSG_FILE Name of DBPowerSuite's messages file. 2009.06.18 21:11:46 psGDropCheckConstraints ER00012 You requested the program's help function. --------------------------------------------------------- - - psGDropRIConstraints - - --------------------------------------------------------- psGDropRIConstraints: From a list of tables in a segment map, psGDropRIConstraints creates a a SQL script to drop all referential integrity constraints on those tables. The SQL will contain dynamic SQL, which in turn, reads the database's system tables for the names of the constraints. If you specify a except tables file, this program will not generate RI drop SQL for the tables listed in the file. Syntax: psGDropRIConstraints -Tsegment_map_file -Eexcept_tables_file -Fnew_sql_script -Nschema_name -Ooptions Arguments: -T Segment Map File -E Except Tables File -N Script Name (optional) -F Output SQL Script [standard output] -O Options s Sybase o Oracle m Microsoft SQL Server i Informix r Postgres y MySQL g ODBC p Stop at the first parsing problem psGDropRIConstraints looks for the environment variables: PS_SYSMSG_FILE Name of DBPowerSuite's messages file. 2009.06.18 21:11:46 psGDropRIConstraints ER00012 You requested the program's help function. --------------------------------------------------------- - - psGDropProcedures - - --------------------------------------------------------- psGDropProcedures: This program drops stored procedures and functions in a database. Syntax: psGDropProcedures -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Hschema_name -Fsql_script -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Drop Procedures belonging to owner only -N Drop Procedures having pattern 'name' [!rs%] -H Schema name (optional) -F Name of output SQL script [standard output] -O Options s Sybase o Oracle m Microsoft SQL Server i Informix r Postgres y MySQL g ODBC psGDropProcedures looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:46 psGDropProcedures ER00012 You requested the program's help function. --------------------------------------------------------- - - psGDropTables - - --------------------------------------------------------- psGDropTables: From a list of tables in a segment map, psGDropTables creates a a SQL script to drop the tables. The tables will be dropped in the same order as they appear in the segment map. Syntax: psGDropTables -Tsegment_map_file -Fnew_sql_file -Nschema_name -Ooptions Arguments: -T Segment Map File -F Output SQL Script [standard output] -N Script Name (optional) -O Options s Sybase o Oracle m Microsoft SQL Server i Informix r Postgres y MySQL g ODBC p Stop at the first parsing problem psGDropTables looks for the environment variables: PS_SYSMSG_FILE Name of DBPowerSuite's messages file. Notes: The purposes of this program is to write a drop script 1. which is taylored for specific vendor databases 2. which checks for the table's existence before dropping the table 3. which drops the tables without violating referential integrity constraints. These 3 purposes are accomplished one way or another. Oracle: My Oracle script looks like it does because none of Oracle's 3 manifestations of SQL is capable of executing an *if* statement with a DDL command. Believe it or not, is impossible with Oracle. I have come to the conclusion that anyone who buys Oracle does not buy Oracle based on technical merits. Sybase: If you are a Sybase user, use psDropTables (not psGDropTables) to drop tables. psDropTables automatically searches the system catalog for referential integrity constraints and automatically writes the script to drop child tables first. psDropTables also drops tables for a specified owner. Other_Databases_and_You_Also_Have_Sybase: If you use Sybase anywhere in your shop and you want to write drop scripts for Oracle, Informix, ODBC or MS SQL Server, you can first run psGenerateSegmentMap -Or against your Sybase database in order to get the correct child/parent table order. If you don't have Sybase, you'll have to figure out the child/parent table order manually. 2009.06.18 21:11:46 psGDropTables ER00012 You requested the program's help function. --------------------------------------------------------- - - psGDropViews - - --------------------------------------------------------- psGDropViews: This program drops views in a database. Syntax: psGDropViews -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Hschema_name -Fsql_script -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Drop views belonging to owner only -N Drop views having pattern 'name' [!rs%] -H Schema name (optional) -F Name of output SQL script [standard output] -O Options s Sybase o Oracle m Microsoft SQL Server i Informix r Postgres y MySQL g ODBC psGDropViews looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:47 psGDropViews ER00012 You requested the program's help function. --------------------------------------------------------- - - psGenerateCCDatatypes - - --------------------------------------------------------- psGenerateCCDatatypes: This program generates C++ code for DBDatatype::dtLoadDefintions(). The program reads a datatype definitions UFile to that end. You can also see what the existing default defintions are. Syntax: psGenerateCCDatatypes -Iinput_definitions_ufile -Foutput_c++_source_file -Ooptions Arguments: -I Input Datatypes Definitions UFile -F Output C++ Source File [standard output] -O Options d Show existing default definitions psGenerateCCDatatypes looks for the environment variables: PS_SYSMSG_FILE Name of DBPowerSuite's messages file. 2009.06.18 21:11:47 psGenerateCCDatatypes ER00012 You requested the program's help function. --------------------------------------------------------- - - psGReindex - - --------------------------------------------------------- psGReindex: From a list of tables or indices in an RI map, psGReindex creates a a SQL script to reindex individual indices or entire tables. You can generate a list of tables or indexes using psOListTables, psOListIndices, psGenerateRIMap or by a clever SQL command via psOSQL or psSQL. Syntax: psGReindex -Tri_map_file -Nschema_name -Fnew_sql_file -Ooptions Arguments: -T RI Map File -N Schema Name (optional) -F Output SQL Script [standard output] -O Options s Sybase o Oracle m Microsoft SQL Server i Informix r Postgres y MySQL g ODBC u Convert all names to upper case l Convert all names to lower case x The list of objects are indices, not tables p Stop at the first parsing problem t Turn tracing on psGReindex looks for the environment variables: PS_SYSMSG_FILE Name of DBPowerSuite's messages file. 2009.06.18 21:11:47 psGReindex ER00012 You requested the program's help function. --------------------------------------------------------- - - psGSegmentSQL - - --------------------------------------------------------- psGSegmentSQL: This program reads in your SQL file and your segment map file and then writes a new SQL file with the tables and indexes created on the segments you specify in the segment map file. In Oracle, segments are known as tablespaces. In Informix, segments are known as dbspaces. In Microsoft SQL Server, segments are known as file groups. Syntax: psGSegmentSQL -Iinput_sql_script -Fnew_sql_file -Tsegment_map_file -Xtable_segment -xnon_clustered_index_segment -iimage_segment -Ooptions Arguments: -I Input SQL Script -F Output SQL Script [standard output] -T Segment Map File -X Target table/clustered index segment -x Target nonclustered index segment -i Target image/text segment -O Options s Sybase o Oracle m Microsoft SQL Server i Informix f Use storage specifications t Show trace messages p Stop at the first parsing problem Notes: I designed this program to read native SQL scripts generated by Sybase's PowerDesigner 8. PowerDesigner 8 cannot generically handle segment and storage options for tables and indexes between databases of different vendors. So, this program fills in that gap. The segment map file contains at least 4 columns of information. The columns are table, table segment name, non-clustered index segment name and image segment name. You can include an optional 5th and 6th columns which specify the storage specifications of the table (column 5) and of the indexes (column 6). psGSegmentSQL looks for the environment variables: PS_SYSMSG_FILE Name of DBPowerSuite's messages file. 2009.06.18 21:11:47 psGSegmentSQL ER00012 You requested the program's help function. --------------------------------------------------------- - - psGSetOracleTableLogging - - --------------------------------------------------------- psGSetOracleTableLogging: From a list of tables in a segment map, psGSetOracleTableLogging creates an Oracle SQLPlus script to turn on or off logging for the tables. Syntax: psGSetOracleTableLogging -Tsegment_map_file -Fnew_sql_file -Nscript_name -Ooptions Arguments: -T Segment Map File -F Output SQL Script [standard output] -N Script Name (optional) -O Options L Turn on logging. [Default is turn off logging.] p Stop at the first parsing problem psGSetOracleTableLogging looks for the environment variables: PS_SYSMSG_FILE Name of DBPowerSuite's messages file. 2009.06.18 21:11:48 psGSetOracleTableLogging ER00012 You requested the program's help function. --------------------------------------------------------- - - psGSetOracleTableParallel - - --------------------------------------------------------- psGSetOracleTableParallel: From a list of tables in a segment map, psGSetOracleTableParallel creates an Oracle SQLPlus script to turn on CPU parallelism on or off for the tables. Syntax: psGSetOracleTableParallel -Tsegment_map_file -Fnew_sql_file -Nscript_name -Ooptions Arguments: -T Segment Map File -F Output SQL Script [standard output] -N Script Name (optional) -O Options Y Turn on parallelism. [Default is turn off parallelism.] p Stop at the first parsing problem psGSetOracleTableParallel looks for the environment variables: PS_SYSMSG_FILE Name of DBPowerSuite's messages file. 2009.06.18 21:11:48 psGSetOracleTableParallel ER00012 You requested the program's help function. --------------------------------------------------------- - - psGTruncateTables - - --------------------------------------------------------- psGTruncateTables: From a list of tables in a RI map, psGTruncateTables creates a a SQL script to truncate the tables. The tables will be truncated in the same order as they appear in the RI map. You can generate a RI map using psGenerateRIMap. Syntax: psGTruncateTables -Tri_map_file -Fnew_sql_file -Nschema_name -Ooptions Arguments: -T RI Map File -F Output SQL Script [standard output] -N Schema Name (optional) -O Options s Sybase o Oracle 8.0 8 Oracle 8.1+ m Microsoft SQL Server i Informix r Postgres y MySQL g ODBC u Convert all names to upper case p Stop at the first parsing problem t Turn tracing on psGTruncateTables looks for the environment variables: PS_SYSMSG_FILE Name of DBPowerSuite's messages file. Notes: The purposes of this program is to write a truncate script 1. which is taylored for specific vendor databases 2. which checks for the table's existence before truncating the table 3. which truncates the tables without violating referential integrity constraints. Sybase: For Sybase, you must provide a RI map with tables listed in child to parent order. I don't look at the referential integrity names for Sybase. Sybase doesn't need them. But if you are a Sybase user, use psTruncateTables (not psGTruncateTables) to truncate tables. psTruncateTables automatically searches the system catalog for referential integrity constraints and automatically writes the script to truncate child tables first. psTruncateTables also truncates tables for a specified owner. At the time of this writing, Sybase is the only database that handles table truncation and foreign key constraints correctly. That is, if all the child tables are empty, Sybase will allow you to truncate the parent tables. Believe it or not, Oracle 8.1 and Microsoft SQL Server 7.0 will not let you do this. Oracle and Microsoft products are too stupid to go check if the child tables are empty. Oracle: For Oracle, you must provide a RI map with outgoing referential integrity constraints with tables listed in child to parent order. I have provided 2 Oracle options. In Oracle 8.0, you can truncate parent tables when their child tables have no rows. In Oracle 8.1, you cannot. For 8.1, I have to disable the foreign key constraints of the child table, even if the child table is empty, before I go ahead and truncate the parent table. Yes, one should consider this a bug in Oracle. Oracle regards this as a feature. Writing Oracle scripts SQL*Plus with PL/SQL is a challenge. Oracle SQL is extremely deficient in features. What is easy to write in Sybase or Microsoft SQL Server is a nightmare in Oracle. One of the reasons why psGTruncateTables must generate a script which spools repetitively to a temporary file is because Oracle lacks the simple SQL language construct of a 'conditional DDL statement.' For example, 'if table exists, truncate table'. This is impossible with Oracle. Yes, one should deem this as a critical problem with Oracle. Given this this one gigantic deficiency, no one should buy Oracle. Oracle, however, thinks this is normal. The goal of the author of DBPowerSuite, is to generate scripts which run without error. No one wants to give scripts to their user communities which blow up. What makes Oracle really awful in this aspect, is that Oracle's programming technique is based on blowing up, and then afterwards, checking for errors. It is literally impossible to write Oracle SQL DDL scripts which don't blow up. I have done the best I can to generate scripts which will not blow under normal circumstances. However, by checking for table existence and then spooling a truncate table SQL statement which executes later, I open a hole in transactional integrity. There is no way around this in Oracle. Oracle, believe it or not, regards this as normal. Microsoft_SQL_Server: For MS SQL Server, you must provide a RI map with incoming referential integrity constraints with tables listed in child to parent order. Microsoft SQL Server 7.0 is a lot like Oracle 8.1 in the sense that the database server cannot truncate a parent table even if the children are rowless. SQL Server 7.0 is worse than Oracle 8.1 because SQL Server provides no way to disable the referential integrity constraints. Therefore for SQL Server, I will truncate the child tables, but I delete from the parent tables. Watch out because the SQL delete command logs transactions. Informix: Informix does not have a truncate table command. Therefore, this program will write 'delete from table' commands. Watch out! Such 'delete' commands are transactions which log. You may want to drop and then. recreate your tables instead. (See psGDropTables.) Other_Databases_and_You_Also_Have_Sybase: If you use Sybase anywhere in your shop and you want to write truncate scripts for Oracle, Informix, ODBC or MS SQL Server, you can first run psGenerateRIMap -Or against your Sybase database in order to get the correct child/parent table order. If you don't have Sybase, you'll have to figure out the child/parent table order manually. 2009.06.18 21:11:48 psGTruncateTables ER00012 You requested the program's help function. --------------------------------------------------------- - - psGUpdateStatistics - - --------------------------------------------------------- psGUpdateStatistics: From a list of tables in a segment map, psGUpdateStatistics creates a a SQL script to update their statistics. The tables' statistics will be updated in the same order as they appear in the segment map. Syntax: psGUpdateStatistics -Tsegment_map_file -Fnew_sql_file -Nschema_name -Ooptions Arguments: -T Segment Map File -F Output SQL Script [standard output] -N Schema Name (optional. This argument is for documentation purposes only.) -O Options s Sybase o Oracle m Microsoft SQL Server i Informix r Postgres y MySQL l Convert names to lower case u Convert names to upper case e Do everything to make the table more efficient p Stop at the first parsing problem t Turn tracing on psGUpdateStatistics looks for the environment variables: PS_SYSMSG_FILE Name of DBPowerSuite's messages file. Notes: The purposes of this program is to write an update statistics script 1. which is taylored for specific vendor databases 2. which checks for the table's existence before updating its statistics 3. 'Do everything' means different things for different databases 2009.06.18 21:11:48 psGUpdateStatistics ER00012 You requested the program's help function. --------------------------------------------------------- - - psGWriteSQL - - --------------------------------------------------------- psGWriteSQL: psGWriteSQL applies each item in a list to your entered printf format statment, to a make a new file of SQL statements. Syntax: psGWriteSQL -Titem_list_file -Xformat -Nschema_name -Fnew_sql_file -Ooptions Arguments: -T Item List File -X printf() Format -F Output SQL Script [standard output] -N Schema Name (optional) -O Options s Sybase o Oracle m Microsoft SQL Server i Informix r Postgres y MySQL g ODBC x Swap schema and item positions in format q Do NOT enclose item in quotes when item is a reserved word psGWriteSQL looks for the environment variables: PS_SYSMSG_FILE Name of DBPowerSuite's messages file. 2009.06.18 21:11:48 psGWriteSQL ER00012 You requested the program's help function. --------------------------------------------------------- - - psIsReservedWord - - --------------------------------------------------------- psIsReservedWord: This program checks to see if your input text is a database reserved word. Syntax: psIsReservedWord -Ttext -Foutput_file Arguments: -T Your text -F Name of file to write results [standard output] psIsReservedWord looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:11:49 psIsReservedWord ER00012 You requested the program's help function. ######################################################### # # # Sybase Adaptive Server Programs # # # ######################################################### --------------------------------------------------------- - - psBCP - - --------------------------------------------------------- psBCP: This program loads your Sybase BCP data file into a table. As it loads, it checks each field in each record of your file to see whether it is compatibile with its corresponding column data type. It checks *before* it copies the data to Adaptive Server. If there is a problem with any field, this program tells you the record number where the problem occurred, and then immediately ends with a *non-zero* exit code. If this program does not detect any problems either by its own algorithms or by messages returned from Sybase's APIs or Adaptive Server, then this program ends with a 0 (successful) exit code. A single error will cause psBCP to exit with a non-zero code. For data type errors, psBCP always tells you the offending line number. If you are getting duplicate row errors, psBCP can only give you the offending range of records. The range of records matches the size of the batch (-b). If you make your batch size 1, then you will know the exact number of the duplicate record. Another advantage of this program over bcp, is that while rows are being copied, you can automatically have the database checkpointed or have its log dumped with truncate_only. These operations will occur after each batch so long as you have the permission to do it. This program opens 2 connections to the Adaptive Server. One to do the bulk copy, the other to do checkpoints, dump transaction logs and everything else. This program copies while it checks. If you just want to check your file, run psCheckBulkCopyFile instead. psCheckBulkCopyFile checks types and ranges for each of your file's columns as well as checks for duplicate rows and indices. As opposed to psBCP psCheckBulkCopyFile does not send data to the SQL Server. Syntax: psBCP -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Ntable -Binput_bcp_file -Hheader_rows -bbatch_size -Crows_to_cache -Acolumn_delimiter_string -Yrow_terminator_string -Lleading_character_to_remove -Ttrailing_character_to_remove -Rreplacment_character_for_embedded_space -mmax_record_len -xmax_image_len -Flog -Ee-mail_address -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Table belongs to this 'owner' -N Table having pattern 'name' -B Name of the input BCP load file [standard input] -b Number of rows in a single xact batch [the entire file] -C Number of rows to cache [1000] -H Header records to ignore [0] -A Column delimiter string [ | ] -Y Row terminator string [operating system default record terminator] -L Leading character to remove -T Trailing character to remove -R Replacement character for an embedded space in a text field -m Maximum record size [4096] -x Maximum image size [100000] -F Log file [standard output] -E E-mail address to report errors -O Options: s Turn off statistics reporting t Truncate table first c Checkpoint after each batch d Dump transaction log with truncate_only after each batch i Explicit identity values are in the BCP load file w Remove leading and trailing white spaces except for the delimiter from BCP file q Remove quotes from text fields u Update statistics at the end a Update 'all' statistics at the end psBCP looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:49 psBCP ER00012 You requested the program's help function. --------------------------------------------------------- - - psBcpIntoTable - - --------------------------------------------------------- psBcpIntoTable: This program writes a script to bulk copy a file into a table. Memory caches are unbound, non-clustered indexes are dropped. Triggers are dropped. DB options are set. And then the file is bcp-ed into the table. After the bcp, indexes are rebuilt and cache bindngs are reestablished. Triggers are added. Update statistics and sp_recompile are run. This program assumes that the input BCP file is in native binary format. If it is in ASCII instead, specify either -Oa or -OA. Syntax: psBcpIntoTable -Sserver -Uusername -Ppassword -Ddatabase -Wtable_owner -Ntable_name -Bbulkcopy_file -Acolumn_delimiter -Yrow_terminator -Ifill_factor -FKorn_shell_script -Rresults_file -Mresults_file_max_size -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Owner of the table -N Name of the table -B Bulk copy file name [owner_table.bcp] -A Column delimiter string ['|' when -Oa specified] -Y Row terminator string [defaults to operating system record terminator] -I Will override any previous index creation with this fill factor. -F Name of output Korn shell script [standard output] -R psSQL results file [standard output] -M psSQL results file initial maximum size in bytes [0] -O Options: t Truncate the table before bcp operation p BCP file's presence is optional l The BCP operation must be recorded in the transaction log a Use character bcp. Column delimiter is -A. A Use character bcp. Tab is the column delimiter. i Your bcp file does not have a placeholder for the identity column e Use the identity column values contained in the bcp file v Make psSQL put timestamps on comments on SQL commands x Execute created script immediately after its created. psBcpIntoTable looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:49 psBcpIntoTable ER00012 You requested the program's help function. --------------------------------------------------------- - - psBindDefaults - - --------------------------------------------------------- psBindDefaults: This program binds table columns to defaults. Only those columns that were bound to defaults created by 'create default' will have sp_bindefault statements generated. Syntax: psBindDefaults -Sserver -Uusername -Ppassword -Ddatabase -Wdefault_owner -Ndefault_name -Vtable_owner -Ttable_name -starget_server -utarget_username -ptarget_pass -dtarget_db -wtarget_default_owner -vtarget_table_owner -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Bind defaults owned by pattern 'default_owner' [-U] -N Bind defaults named by pattern 'default_name' -V Bind defaults to those tables belonging to owner only [-U] -T Bind defaults to those tables named 'name' -s Target Sybase Server [-S] -u Target Sybase User Name [-U] -p Target Sybase User's Password [-P] -d Target Sybase Database [-D] -v Target Table Owner [-V] -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psBindDefaults looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:50 psBindDefaults ER00012 You requested the program's help function. --------------------------------------------------------- - - psBindRules - - --------------------------------------------------------- psBindRules: This program binds table columns to rules. Only those columns that were bound to rules created by 'create rule' will have sp_bindrule statements generated. Syntax: psBindRules -Sserver -Uusername -Ppassword -Ddatabase -Wrule_owner -Nrule_name -Vtable_owner -Ttable_name -starget_server -utarget_username -ptarget_pass -dtarget_db -wtarget_default_owner -vtarget_table_owner -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Bind rules owned by pattern 'rule_owner' [-U] -N Bind rules named by pattern 'rule_name' -V Bind rules to those tables belonging to owner only [-U] -T Bind rules to those tables named 'name' -s Target Sybase Server [-S] -u Target Sybase User Name [-U] -p Target Sybase User's Password [-P] -d Target Sybase Database [-D] -v Target Table Owner [-V] -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psBindRules looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:50 psBindRules ER00012 You requested the program's help function. --------------------------------------------------------- - - psCheckBulkCopyFile - - --------------------------------------------------------- psCheckBulkCopyFile: This program compares your Sybase BCP load file against the Sybase's table's column and index definitions to see whether or not your file will load properly into the table. Each field in your BCP load file is checked for compatibility with its column data type. Also, field combinations are checked for uniqueness if the table has unique indexes. If the table has a clustered index which disallows duplicates, then this program checks for row duplicates. The purpose of this program is to quickly scan your BCP load file for errors. You could let the BCP program do it as well, but BCP can take hours longer because your data is being written into Sybase at the same time. Also, once BCP finds an error, Sybase rejects your transaction batch and many times BCP will not tell you exactly what record in your file is bad. Syntax: psCheckBulkCopyFile -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Ntable -Binput_bcp_file -boutput_bcp_file -Ainput_column_delimiter -aoutput_column_delimiter -Yinput_row_terminator -youtput_row_terminator -mmax_record_len -xmax_image_len -Flog -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Table belongs to this 'owner' -N Table having pattern 'name' -B Name of the input BCP load file -b Name of the output BCP load file [optional] -A Input column delimiter string [|] -a Output column delimiter string [Input column delimiter string] -Y Input row terminator string [defaults to operating system record terminator] -y Output row terminator string [defaults to input row terminator] -m Maximum record size [100000] -x Maximum image/text size [100000] -F Log file [standard output] -O Options: w Remove leading and trailing white spaces from BCP load file i Explicit identity values are in the BCP load file n Do not check for duplicates r Write bad records to the log file Notes: If you do not specify a column delimiter and row terminator, this program assumes your text file has tab delimiters and standard operating system record terminators (a newline character (\n) in UNIX.) If you specify a delimiter or terminator, enclose your string in single quotes. A single quote (') will allow you to also use the '$' character. As for special white space characters, I only decode the following because Sybase's bcp only understands these: \t tab \n newline \r return A backslash proceeding any other character renders the character. If you specify a number, then I assume that number represents the ASCII code for the delimiter or terminator. Avoid the number '0', i.e., null. If your records contain embedded nulls, you have more problems than you realize. Note the very powerful option of -b, -a and -y. Used together, you can translate the column delimiters and row terminators in your input file to something totally different. The -b option, in particular, acts as a filter. By using -b to specify an output file, this program will generate a new bcp file which contains only good records and new delimiters. psCheckBulkCopyFile looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:50 psCheckBulkCopyFile ER00012 You requested the program's help function. --------------------------------------------------------- - - psCheckDB - - --------------------------------------------------------- psCheckDB: This program writes dbcc checkalloc(database, fix) for all your specified databases. It will skip the master database. Syntax: psCheckDB -Sserver -Uusername -Ppassword -Ddatabase_pattern -FKorn_script -Llog_file -Mmax_msg_log_file_size -Ttime_out -Eemail_address_when_error -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database Name Pattern [!tempdb] -F Korn Shell script name -L Messages Log File. [standard output] -M Maximum Size of Log File [1,000,000 bytes] (Used only when you specify -F) -E Email address to send errors when there are errors -T Time out -O Options: f Do not fix the allocation chains x Execute script immediately [-F is required] psCheckDB looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:51 psCheckDB ER00012 You requested the program's help function. --------------------------------------------------------- - - psCheckpoint - - --------------------------------------------------------- psCheckpoint: This program does a checkpoint in every available database. In order for this occur properly, the user must have sa_role. Syntax: psCheckpoint -Sserver -Uusername -Ppassword -Flog_file -Mmax_init_log_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User Password -F Log file -M Maximum initialize size of log file in bytes [0] psCheckpoint looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:51 psCheckpoint ER00012 You requested the program's help function. --------------------------------------------------------- - - psCheckRIConstraints - - --------------------------------------------------------- psCheckRIConstraints: This program writes a script to check the referential integrity of user tables in a database. Such a check is required if you are using constraints and you have bulk copied data into the tables. When loading data into tables using bcp, bcp ignores referential integrity constraints. Syntax: psCheckRIConstraints -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Vviolations -FKorn_script -Rresult_file -Mresults_file_max_size -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Use tables belonging to 'owner' only -N Use tables having pattern 'name' [!rs%] -V Stop checking table after this number of violations [50] -F Name of output Korn shell script [standard output] -R Name of psSQL's results file [standard output] -M Maximum size of psSQL's results file [0] -O Options: c Do not comment the results file x Just check those constraints which cross databases r Just check normal RI constraints (default) k Just check foreign keys defined by sp_foreignkey j Just check keys defined with sp_commonkey psCheckRIConstraints looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:51 psCheckRIConstraints ER00012 You requested the program's help function. --------------------------------------------------------- - - psCheckRules - - --------------------------------------------------------- psCheckRules: This program checks whether your tables' columns violate their check constraints and rules. If you are concerned about the integrity of your data, this program as well as psCheckRIConstraints are programs which confirm or deny your suspicions. Bad data can enter your tables in many ways. People can drop the constraints. Bulk copy operations ignore constraints. And Mother Nature herself can send a thunderbolt to corrupt the disk itself. dbcc checks for acts of Mother Nature. psCheckRIConstraints and psCheckRules check for evil deeds done by users. This program generates a Korn shell script. The script contains an an embedded psSQL session. The psSQL session runs SQL which lists the rows which are violating their rules. Syntax: psCheckRules -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Vmax_violations_to_display -Rresults_file -Mresults_file_max_size -Ooptions Arguments: -S Source Sybase Server Name -U Source Sybase User Name -P Source Sybase User Password -D Source Sybase Database -W Check tables owned by 'owner' only [-U] -N Check tables having pattern 'name' [!rs%] -V Display up this many violations per table [50] -F Name of output Korn shell script [standard output] -R psSQL results file name [standard output] -M psSQL initial maximum size in bytes for results file [0] -O Options: c Do not comment the results file psCheckRules looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:11:51 psCheckRules ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopyDefaults - - --------------------------------------------------------- psCopyDefaults: This program copies defaults from one schema to another. This program can only copy defaults of a single owner at a time. Also, psCopyDefaults only copies those defaults that were created with the 'create default' command. Defaults created within 'create table' commands are not copied. Syntax: psCopyDefaults -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -starget_server -utarget_username -ptarget_pass -dtarget_db -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Copy defaults belonging to owner only [-U] -N Copy defaults whose name has pattern 'name' [!rs%] -s Target Sybase Server -u Target Sybase User Name -p Target Sybase User's Password -d Target Sybase Database -w Target Default Owner [-u] -F Name of output Korn shell script. [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psCopyDefaults looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:52 psCopyDefaults ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopyIndices - - --------------------------------------------------------- psCopyIndices: This program copies indices from one schema to another. The program copies both constraint and non-constraint indices. By default, only the non-clustered indices are copied. If you want the clustered indices copied as well, specify the -Oc option. You can copy the indices to different segments with the -T option. With -T you specify the name of an input text file. The the text file contains a table with 3 columns: Table Segment Non-clustered Index Segment You can generate this file by running psGenerateSegmentMap. An example segment map file is in: /disk2/ceccareb/PS/dbaccess/demo/psGenerateSegmentMap If you do not put an entry for the segment column, I assume you want the segment specified by the -X option. If you don't specify an entry for the non-clustered index segment, I assume you want the segment specified by the -x (lower case) option. If you don't specify -X or -x, the target segments are the same as the source segments. Syntax: psCopyIndices -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -starget_server -utarget_user -ptarget_pass -dtarget_db -wtarget_owner -Tsegment_map -Xtable_segment -xnon-clustered_segment -Ifill_factor -Rresults_file -Mresults_file_max_size -Ooptions Arguments: -S Source Sybase Server Name -U Source Sybase User Name -P Source Sybase User Password -D Source Sybase Database -W Copy indices of tables belonging to 'owner' only [-U] -N Copy indices of tables having pattern 'name' [!rs%] -s Target Sybase Server Name [-S] -u Target Sybase User Name [-U] -p Target Sybase User Password [-P] -d Target Sybase Database [-D] -w Make target indices owned by 'owner' [-W] -T Text file containing a table to segment map -X Target table/clustered index segment -x Target non-clustered index segment -I Index fill factor -F Name of output Korn shell script [standard output] -R psSQL results file name [standard output] -M psSQL initial maximum size in bytes for results file [0] -O Options: c Copy clustered indices too t Do NOT check for existence of target server psCopyIndices looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:11:52 psCopyIndices ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopyKeys - - --------------------------------------------------------- psCopyKeys: This program writes a script which copies the keys on user tables that were created with sp_primarykey, sp_foreignkey and sp_commonkey. Syntax: psCopyKeys -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -starget_server -utarget_user -ptarget_pass -dtarget_db -wtarget_owner -FKorn_script -Rresults_file -Mmax_size_results_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Copy keys on tables belonging to 'owner' only -N Copy keys on tables whose name have pattern 'name' [!rs%] -s Target Sybase Server Name -u Target Sybase User Name -p Target Sybase User's Password -d Target Sybase Database -w Target owner of keys -F Name of output UNIX Korn shell script [standard output] -R psSQL results file [standard output] -M Maximum size in bytes of psSQL results file [0] psCopyKeys looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:52 psCopyKeys ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopyProcedures - - --------------------------------------------------------- psCopyProcedures: This program copies procedures and functions from one schema to another. This program can only copy procedures of a single owner at a time. Syntax: psCopyProcedures -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -starget_server -utarget_username -ptarget_pass -dtarget_db -FKorn_script -Rresults_file -Mresults_file_max_size -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Copy procedures belonging to owner only [-U] -N Copy procedures whose name has pattern 'name' [!rs%] -s Target Sybase Server -u Target Sybase User Name -p Target Sybase User's Password -d Target Sybase Database -w Target Procedure Owner [-u] -F Name of output Korn shell script. [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] -O Options y Ignore creation dependencies on other procedures e Ignore all Sybase errors. Always return successful from psSQL. psCopyProcedures looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:53 psCopyProcedures ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopyRIConstraints - - --------------------------------------------------------- psCopyRIConstraints: This program writes a Korn Shell script which adds referential integrity constraints back to user tables in a database. Syntax: psCopyRIConstraints -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -starget_server -utarget_username -ptarget_password -dtarget_database -wtarget_owner -FKorn_script -Rresults_file -Mmax_size_results_file -Ooptions Arguments: -S Sybase Server Name -U Sybase Username -P Sybase User's Password -D Sybase Database -W Use tables belonging to 'owner' only -N Use tables having pattern 'name' [!rs%] -s Target Sybase server name [-S] -u Target Sybase username [-U] -p Target Sybase password [-P] -d Target Sybase database [-D] -w Target Sybase table owner [-W] -F Name of output Korn shell script [standard output] -R psSQL results file [standard output] -M Maximum size in bytes of psSQL results file [0] -O Options: x Just add the constraints which cross databases. psCopyRIConstraints looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:53 psCopyRIConstraints ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopyRules - - --------------------------------------------------------- psCopyRules: This program copies rules from one schema to another. This program can only copy rules of a single owner at a time. Also, psCopyRules only copies those rules that were created with the 'create rule' command. Rules created within 'create table' commands are not copied. Syntax: psCopyRules -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -starget_server -utarget_username -ptarget_pass -dtarget_db -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Copy rules belonging to owner only [-U] -N Copy rules whose name has pattern 'name' [!rs%] -s Target Sybase Server -u Target Sybase User Name -p Target Sybase User's Password -d Target Sybase Database -w Target Rule Owner [-u] -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psCopyRules looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:53 psCopyRules ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopySchema - - --------------------------------------------------------- psCopySchema: This program writes a script to copy tables' schema from one database to another. Optionally, you can drop the target tables prior to adding the new schema. Syntax: psCopySchema -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -starget_server -utarget_username -ptarget_password -dtarget_database -wtarget_owner -Ifill_factor_for_indices -Tsegment_map -Xtarget_table_seg_def -xtarget_nc_index_seg -iimage_seg -Bbulk_copy_directory -Acolumn_delimiter_string -Yrow_terminator_string -Foutput_Korn_shell_file -Rresults_file -Mresults_file_max_size -Ooptions Arguments: -S Source Sybase Server Name -U Source Sybase User Name -P Source Sybase User's Password -D Source Sybase Database -W Use tables in source database belonging to 'owner' - REQUIRED -N Use tables in source database having pattern 'name' [!rs%] -s Target Sybase Server Name -u Target Sybase User Name -p Target Sybase User's Password -d Target Sybase Database -w Change tables in target database to belong to 'target_owner' -I Fill factor for target indices (optional) -T Segment map (optional) -X Target table/clustered index segment (optional) -x Target non-clustered index segment (optional) -i Image/text segment (optional) -B Bulk copy directory if you plan to bulk copy [.] -A Column delimiter string for character bulk copies [|] -Y Row terminator string for character bulk copies [operating system default record terminator] -F Name of output UNIX Korn shell script [standard output] -R psSQL results file name [standard output] -M Initial maximum size in bytes of psSQL results file [0] -O Options: e copy everthing: it is the equivalent of (PvuDR). 'e' copies everything but data. d Drop target objects N Use Sybase native datatypes p Copy stored procedures too P Copy stored procedures but ignore any errors v Copy views too u Copy user-defined datatypes too j Bind 'created defaults' too k Bind 'created rules' too D Copy and bind 'created defaults' too R Copy and bind 'created rules' too n Write a script that does NOT modify any data t Do NOT check if target server is available x Execute script immediately c Clean up (delete) scripts if entire process finishes successfully b BCP bulk copy data to target too. Your (-B) directory must be big enough to store all the BCP files. a Bulk copy data to target using character mode. -A arg is the column delimiter string. A Bulk copy data to target using character mode. Use the tab as the column delimiter. i Bulk copy data to target using existing BCP files o Bulk copy data out, not in psCopySchema looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file Notes: This program copies a schema from one Sybase server to another. You can also copy the data. If you just want to create schema and bcp files without actually transferring them to the target, use options n, o and t. 2009.06.18 21:11:54 psCopySchema ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopyTableData - - --------------------------------------------------------- psCopyTableData: This program writes a script to copy tables from one database to another. The program uses Sybase's bcp to copy out the tables and psBcpIntoTable to copy them into the target database. Syntax: psCopyTableData -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -starget_server -utarget_username -ptarget_password -dtarget_database -wtarget_owner -Bbcp_out_directory -bbcp_in_directory -Acolumn_delimiter_string -Yrow_terminator_string -Ifill_factor_for_indices -Foutput_Korn_script -Rresults_file -Mresults_file_max_size -Ooptions Arguments: -S Source Sybase Server Name -U Source Sybase User Name -P Source Sybase User's Password -D Source Sybase Database -W Use source database tables belonging to 'owner' - REQUIRED -N Use source database tables having pattern 'name' [!rs%] -s Target Sybase Server Name -u Target Sybase User Name -p Target Sybase User's Password -d Target Sybase Database -w Change ownership of tables in target database to 'target_owner' -B BCP Out directory -b BCP In directory [BCP out directory] -A Column delimiter string ['|' when -Oa specified] -Y Row terminator string [defaults to operating system standard terminator] -I Fill factor for target indices -F Name of output UNIX Korn shell script [standard output] -R psSQL results file [standard output] -M psSQL results file initial maximum size in bytes [0] -O Options: o Only write the bcp-out commands i Only write the bcp-in commands p The presence of BCP files for input is optional a Use character bcp. Column delimiter is -A. A Use character bcp. Use the tab as the column delimiter. t Truncate target database tables v Put time stamps on psSQL comments c Suppress target table compatibility test d Delete intermediate files at end (o and i required) z Do bcps for zero row count tables too psCopyTableData looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:54 psCopyTableData ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopyTables - - --------------------------------------------------------- psCopyTables: This program copies column definitions of tables of one owner to a target owner. The target owner can be on a different SQL server or database. By default, if the table has a clustered index, the clustered index is copied along with the table definition. By default, the datatypes of columns are transferred in their Sybase native datatypes. If you want the transfer to occur using your own user-defined types, then specify the -Ou option. Nullness and identity attributes are preserved. Check constraints and column defaults are also preserved. However, if you need to transfer bindings to defaults and rules that were created with 'create default' and 'create rule' commands, you need to run psCopyDefaults, psCopyRules, psBindDefaults and psBindRules also. You can copy the indices to different segments with the -T option. With -T you specify the name of an input text file. The the text file contains a table with 4 columns. I ignore the third column. Table Segment Non-clus Index Segment Image/Text Segment You can generate this file by running psGenerateSegmentMap. An example segment map is in: /disk2/ceccareb/PS/dbaccess/demo/psGenerateSegmentMap If you do not put an entry for the segment column, I assume you want the segment specified by the -X option. If you don't specify -X, the target segments are the same as the source segments. Syntax: psCopyTables -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -starget_server -utarget_user -ptarget_pass -dtarget_db -wtarget_owner -Tsegment_map -Xtable_segment -iimage_segment -Ifill_factor -Rresults_file -Mresults_file_max_size -Ooptions Arguments: -S Source Sybase Server Name -U Source Sybase User Name -P Source Sybase User Password -D Source Sybase Database -W Copy definitions of tables definitions belonging to 'owner' only [-U] -N Copy definitions of tables having pattern 'name' [!rs%] -s Target Sybase Server Name [-S] -u Target Sybase User Name [-U] -p Target Sybase User Password [-P] -d Target Sybase Database [-D] -w Make target indices owned by 'owner' [-W] -T Text file containing a table to segment map -X Target table/clustered index segment -i Target image/text segment -I Index fill factor -F Name of output Korn shell script [standard output] -R psSQL results file name [standard output] -M psSQL initial maximum size in bytes for results file [0] -O Options: c Do NOT copy clustered indices t Do NOT check for existence of target server u Use user-defined datatypes psCopyTables looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:11:54 psCopyTables ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopyTriggers - - --------------------------------------------------------- psCopyTriggers: This program copies triggers from one schema to another. This program can only copy triggers of a single owner at a time. Syntax: psCopyTriggers -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Ttable -starget_server -utarget_username -ptarget_pass -dtarget_db -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Copy triggers belonging to owner only [-U] -N Copy triggers that have pattern 'name' -T Copy triggers only on those tables that match pattern 'table' [!rs%] -s Target Sybase Server -u Target Sybase User Name -p Target Sybase User's Password -d Target Sybase Database -w Target Trigger Owner [-u] -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psCopyTriggers looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:55 psCopyTriggers ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopyUserTypes - - --------------------------------------------------------- psCopyUserTypes: This program copies user-defined types from one schema to another. This program can only copy user-defined datatypes of a single owner at a time. If any of your datatypes have defaults or rules bound to them, these rules and defaults must already exist in the target database. Syntax: psCopyUserTypes -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -starget_server -utarget_username -ptarget_pass -dtarget_db -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Copy user-defined datatypes belonging to owner only [-U] -N Copy user-defined datatypes whose name has pattern 'name' [!rs%] -s Target Sybase Server -u Target Sybase User Name -p Target Sybase User's Password -d Target Sybase Database -w Target Default Owner [-u] -F Name of output Korn shell script. [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psCopyUserTypes looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:55 psCopyUserTypes ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopyViewKeys - - --------------------------------------------------------- psCopyViewKeys: This program writes a script which copies the keys on user views that were created with sp_primarykey, sp_foreignkey and sp_commonkey. Syntax: psCopyViewKeys -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -starget_server -utarget_user -ptarget_pass -dtarget_db -wtarget_owner -FKorn_script -Rresults_file -Mmax_size_results_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Copy keys on views belonging to 'owner' only -N Copy keys on views whose name have pattern 'name' [!rs%] -s Target Sybase Server Name -u Target Sybase User Name -p Target Sybase User's Password -d Target Sybase Database -w Target owner of keys -F Name of output UNIX Korn shell script [standard output] -R psSQL results file [standard output] -M Maximum size in bytes of psSQL results file [0] psCopyViewKeys looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:55 psCopyViewKeys ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopyViews - - --------------------------------------------------------- psCopyViews: This program copies views from one schema to another. This program can only copy views of a single owner at a time. Syntax: psCopyViews -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -starget_server -utarget_username -ptarget_pass -dtarget_db -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Copy views belonging to owner only [-U] -N Copy views whose name has pattern 'name' [!rs%] -s Target Sybase Server -u Target Sybase User Name -p Target Sybase User's Password -d Target Sybase Database -w Target View Owner [-u] -F Name of output Korn shell script. [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psCopyViews looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:56 psCopyViews ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropDefaults - - --------------------------------------------------------- psDropDefaults: This program drops defaults in a database. I drop only those defaults that were created with 'create default' commands. Syntax: psDropDefaults -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Drop defaults belonging to owner only -N Drop defaults having pattern 'name' [!rs%] -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psDropDefaults looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:56 psDropDefaults ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropDevices - - --------------------------------------------------------- psDropDevices: This program drops devices on a SQL Server. Syntax: psDropDevices -Sserver -Uusername -Ppassword -Ndevice_name_pattern -vmin_vdevno -Vmax_vdevno -FKorn_script -Rresults_file -Mresults_file_max_size -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -N Drop devices with this name pattern -v The mininum virtual device number to drop [1] -V The maximum virtual device number to drop [256] -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] -O Options a All devices. By default, just disk devices are dropped. psDropDevices looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:56 psDropDevices ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropIndices - - --------------------------------------------------------- psDropIndices: This program drops indices on tables in a database. The indexes on system tables are not touched. This program will issue either alter table or drop index commands if the index is part of a constraint or not. Before you drop indices, you might have to first drop referential integrity constraints with psDropRIConstraints. Syntax: psDropIndices -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Rresults_file -Mresults_file_max_size -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User Password -D Sybase Database -W Drop indices on tables belonging to 'owner' only -N Drop indices on tables having pattern 'name' [!rs%] -F Name of output Korn shell script [standard output] -R psSQL results file name [standard output] -M Initial max size in bytes for psSQL results file [0] -O Options: c Drop clustered indices too psDropIndices looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:11:57 psDropIndices ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropKeys - - --------------------------------------------------------- psDropKeys: This program writes a script which drops the keys on user tables that were created with sp_primarykey, sp_commonkey and sp_foreignkey. Syntax: psDropKeys -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -FKorn_script -Rresults_file -Mmax_size_results_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Drop keys from tables belonging to 'owner' only -N Drop keys from tables whose name have pattern 'name' [!rs%] -F Name of output UNIX Korn shell script [standard output] -R psSQL results file [standard output] -M Maximum size in bytes of psSQL results file [0] psDropKeys looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:57 psDropKeys ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropProcedures - - --------------------------------------------------------- psDropProcedures: This program drops stored procedures and functions in a database. Syntax: psDropProcedures -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Drop procedures belonging to owner only -N Drop procedures having pattern 'name' [!rs%] -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psDropProcedures looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:57 psDropProcedures ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropRIConstraints - - --------------------------------------------------------- psDropRIConstraints: This program writes a script which drops the referential integrity constraints on user tables in a database. Syntax: psDropRIConstraints -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -FKorn_script -Rresults_file -Mmax_size_results_file -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Use tables belonging to 'owner' only -N Use tables whose name have pattern 'name' [!rs%] -F Name of output UNIX Korn shell script [standard output] -R psSQL results file [standard output] -M Max size in bytes of psSQL results file [0] -O Options: x Just drop the constraints which cross databases. psDropRIConstraints looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:57 psDropRIConstraints ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropRules - - --------------------------------------------------------- psDropRules: This program drops rules in a database. I drop onlythose rules that were created with 'create rule' commands. Syntax: psDropRules -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Drop rules belonging to owner only -N Drop rules having pattern 'name' [!rs%] -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psDropRules looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:58 psDropRules ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropSegments - - --------------------------------------------------------- psDropSegments: This program drops non-system segments in a database. I will not drop the default, system, logsegment or auditseg under any circumstances. Syntax: psDropSegments -Sserver -Uusername -Ppassword -Ddatabase -Nsegment_name_pattern -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -N Drop segments with this name pattern -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psDropSegments looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:58 psDropSegments ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropTables - - --------------------------------------------------------- psDropTables: This program drops user tables in a database. System tables are not touched. Before you drop the tables, you might have to first consider dropping the referential integrity constraints. You can drop them with the -Or option or by running psDropRIConstraints. Syntax: psDropTables -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -FKorn_script -Rresults_file -Mresults_file_max_size -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User Password -D Sybase Database -W Drop tables belonging to 'owner' only -N Drop tables having pattern 'name' [!rs%] -F Name of output Korn shell script [standard output] -R psSQL results file name [standard output] -M psSQL initial max size in bytes for results file [0] -O Options: r Drop referential integrity constraints first t Drop triggers explicity first k Drop sp_primarykeys, sp_commonkeys and sp_foreignkeys first psDropTables looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:11:58 psDropTables ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropTriggers - - --------------------------------------------------------- psDropTriggers: This program drops triggers in a database. Syntax: psDropTriggers -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Ttable -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Drop triggers belonging to owner only -N Drop triggers having pattern 'name' -T Drop triggers only on tables that have pattern 'table' [!rs%] -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psDropTriggers looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:11:59 psDropTriggers ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropUserTypes - - --------------------------------------------------------- psDropUserTypes: This program drops user-defined datatypes in a database. Syntax: psDropUserTypes -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Drop user-defined datatypes belonging to owner only -N Drop user-defined datatypes having pattern 'name' [!rs%] -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psDropUserTypes looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:59 psDropUserTypes ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropViewKeys - - --------------------------------------------------------- psDropViewKeys: This program writes a script which drops the keys on view that were created with sp_primarykey, sp_commonkey and sp_foreignkey. Syntax: psDropViewKeys -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -FKorn_script -Rresults_file -Mmax_size_results_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Drop keys from views belonging to 'owner' only -N Drop keys from views whose name have pattern 'name' [!rs%] -F Name of output UNIX Korn shell script [standard output] -R psSQL results file [standard output] -M Maximum size in bytes of psSQL results file [0] psDropViewKeys looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:11:59 psDropViewKeys ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropViews - - --------------------------------------------------------- psDropViews: This program drops views in a database. Syntax: psDropViews -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Drop views belonging to owner only -N Drop views having pattern 'name' [!rs%] -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psDropViews looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:00 psDropViews ER00012 You requested the program's help function. --------------------------------------------------------- - - psDumpDatabase - - --------------------------------------------------------- psDumpDatabase: This program dumps a database. You can dump the database to disk or to tape. You can dump the database to any Sybase Backup Server so long as the ASE Server knows about the Backup Server. You can dump the database in one or more stripes. You can tell psDumpDatabase to generate dump file names for you. If you specify generated dump file names, the file names will embed a time signature in Universal Time. psDumpDatabase exits with a 0 if your dump completed successfully. If Sybase was unable to dump the database, then psDumpDatabase exits with a non-zero return code. Whether or not the dump was successful, psDumpDatabase writes text messages to your messages log file. If you wish to retrieve the text message associated with the non-zero exit code, then run psShowMessage. Syntax: psDumpDatabase -Sserver -Uusername -Ppassword -Ddatabase -Bbackup_server_name -Xdump_db_directory -Ldump_db_file -Rlist_of_stripes_file -Fmessage_log_file -Mmax_msg_log_file_size -Ttime_out -Ctape_capacity_GB -Eemail_address_when_error -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -B Backup Server Name [SYB_BACKUP] -X Database Dump File Directory -L Database Dump File Name (If absent, the name will be generated.) -R Input Text File Containing a List of Stripe Files (Specify either both -X and -L, or just -R) -T Time out in seconds. [18000 seconds] -C The Tape's Capacity in Gigabytes (If you use -C, you are dumping to a tape. If you do not use -C, you are dumping to disk.) -F Messages Log File. [standard output] -M Maximum Size of Log File [1,000,000 bytes] (Used only when you specify -F) -E E-mail address to send error messages when there are errors -O Options: i Initialize tape (write over previous contents) r Rewind and unload tape when done l Log full informational messages a Do not log abbreviated informational messages psDumpDatabase looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. Notes: ---------------------------------------------------------------- -R If you specify -R, the input file containing the list of stripes, your text file can have 2 columns like: directory_name_1 stripe_file_name_1 directory_name_2 stripe_file_name_2 . . . . . . directory_name_n stripe_file_name_n Or your text file can have just 1 column: directory_name_1 directory_name_2 . . . directory_name_n If you specify just a single column of directory names, then the stripe file names are generated. The generated names look like: SERVER_DB_yyyymmddhhmmssUT.db_dump_N where SERVER is the SQL Server name, DB is the database name, and N is the stripe number. ---------------------------------------------------------------- 2009.06.18 21:12:00 psDumpDatabase ER00012 You requested the program's help function. --------------------------------------------------------- - - psDumpServer - - --------------------------------------------------------- psDumpServer: This program writes a script which dumps all the databases of a SQL Server. If you using the -Ot option, then this program dumps the transaction logs. By default, your databases are dumped to disk and then the dump files are compressed. And by default, all databases except tempdb are dumped. Note that if you want your dump files compressed (default), then you must run the generated script on the same machine where the Backup Server is running. Also, you must have write permission into the directory where Backup Server creates the dumps. If you do not compress dump files, it doesn't matter where you run the generated script. The only requirement is that the Backup Server has permissions to write into the dump directory. As in all DBPowerSuite, this program and its generated script will exit with a 0 if the dumps were successfully written. But note that if you use the -Ox option, you will not be able to retrieve the exit code of the generated script. Syntax: psDumpServer -Sserver -Uusername -Ppassword -Ddatabase_pattern -Bbackup_server_name -Xdump_db_directory -FKorn_script -Mmax_msg_log_file_size -Ttime_out -Ctape_capacity_GB -Eemail_address_when_error -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database Name Pattern [!tempdb] -B Backup Server Name [SYB_BACKUP] -X Database Dump File Directory -T Time out in seconds. [18000 seconds] -C The Tape's Capacity in Gigabytes (If you use -C, you are dumping to a tape. If you do not use -C, you are dumping to disk.) -F Korn Shell script name -L Messages Log File. [standard output] -M Maximum Size of Log File [1,000,000 bytes] (Used only when you specify -F) -E Email address to send errors when there are errors -O Options: t Dump transaction logs only c Do not compress disk dump files l Log full informational messages a Do not log abbreviated informational messages x Execute script immediately [-F is required] psDumpServer looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:00 psDumpServer ER00012 You requested the program's help function. --------------------------------------------------------- - - psDumpTransactionLog - - --------------------------------------------------------- psDumpTransactionLog: This program dumps a database's transaction log. You can dump the log to disk or to tape. You can dump the transaction log to any Sybase Backup Server so long as the ASE Server knows about the Backup Server. You can dump the log in one or more stripes. You can tell psDumpTransactionLog to generate dump file names for you. If you specify generated dump file names, the file names will embed a time signature in Universal Time. You can also truncate the Sybase database transaction log without creating any dump file. psDumpTransactionLog exits with a 0 if your dump completed successfully. If Sybase was unable to dump the log, then psDumpTransactionLog exits with a non-zero return code. Whether or not the dump was successful, psDumpTransactionLog writes text messages to your messages log file. If you wish to retrieve the text message associated with the non-zero exit code, then run psShowMessage. Syntax: psDumpTransactionLog -Sserver -Uusername -Ppassword -Ddatabase -Bbackup_server_name -Xxact_log_directory -Lxact_log_file -Rlist_of_stripes_file -Fmessage_log_file -Mmax_msg_log_file_size -Ttime_out -Ctape_capacity_GB -Ooptions -Eemail_address_when_error Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -B Backup Server Name [SYB_BACKUP] -X Transaction Log Directory -L Transaction Log File Name (If absent, the name will be generated.) -R Input Text File Containing a List of Stripe Files (Specify either both -X and -L, or just -R) -T Time out in seconds. [3600 seconds] -C The Tape's Capacity in Gigabytes (If you use -C, you are dumping to a tape. If you do not use -C, you are dumping to disk.) -F Messages Log File [standard output] -M Maximum Size of Log File [1,000,000 bytes] (Used only when you specify -F) -E Email address to send error when there are errors -O Options: i Initialize tape (write over previous contents) r Rewind and unload tape when done t Dump the transaction log with truncate_only n Dump the transaction log with no_log l Log full informational messages a Do not log abbreviated informational messages psDumpTransactionLog looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. Notes: ---------------------------------------------------------------- -R If you specify -R, the input file containing the list of stripes, your text file can have 2 columns like: directory_name_1 stripe_file_name_1 directory_name_2 stripe_file_name_2 . . . . . . directory_name_n stripe_file_name_n Or your text file can have just 1 column: directory_name_1 directory_name_2 . . . directory_name_n If you specify just a single column of directory names, then the stripe file names are generated. The generated names look like: SERVER_DB_yyyymmddhhmmssUT.xact_dump_N where SERVER is the SQL Server name, DB is the database name, and N is the stripe number. ---------------------------------------------------------------- 2009.06.18 21:12:01 psDumpTransactionLog ER00012 You requested the program's help function. --------------------------------------------------------- - - psEstimateSpace - - --------------------------------------------------------- psEstimateSpace This program estimates the sizes of tables and indexes in a database. The program tallies the space requirements by your target segment. Syntax: psEstimateSpace -Sserver -Uusername -Ppassword -Ddatabase -Kpage_size_in_kilobytes -Tinput_table_list -Foutput_report_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User Password -D Sybase Database -T Table List File used for Input -K Page size in KB [2] -F File name containing space requirements report psEstimateSpace looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file Notes: This program reads a text file containing a list of tables. The text file contains up to 10 columns: 1. template table name 2. template table owner 3. target table/clustered index segment 4. non-clustered indices segment 5. image/text segment 6. estimated number of rows 7. extrapolation technique (Max, Half or Avg) [Max] 8. rows to sample when extrapolation technique is Avg [100] 9. fill factor [0] 10. ps_int16 table description making template table name more specific Check the demo directory for an example text file: /disk2/ceccareb/PS/dbaccess/demo/psEstimateSpace You can generate this text file by running psEstimateSpaceFile. psEstimateSpaceFile will make a list of the current tables in the database following the 9 column format. 2009.06.18 21:12:01 psEstimateSpace ER00012 You requested the program's help function. --------------------------------------------------------- - - psEstimateSpaceFile - - --------------------------------------------------------- psEstimateSpaceFile: This program makes a table list file for input into program psEstimateSpace. Syntax: psEstimateSpaceFile -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Xtable_segment -xnon_clustered_index_segment -iimage_segment -Ifill_factor -Foutput_table_list_file -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User Password -D Sybase Database -W Use tables belonging to 'owner' only -N Use tables having pattern 'name' -X Target table/clustered index segment -x Target non-clustered index segment -i Target image/text segment -I Index fill factor -F Name of output table list file for psEstimateSpace [standard output] -O Options: m Force extrapolation method for var columns to max size h Force extrapolation method for var columns to half size psEstimateSpaceFile looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file Notes: By default, the extrapolation method for var columns is the average size of a sample of 100 rows. 2009.06.18 21:12:01 psEstimateSpaceFile ER00012 You requested the program's help function. --------------------------------------------------------- - - psFindProcedure - - --------------------------------------------------------- psFindProcedure: This program finds a stored procedure, extended stored procedure or java function in a Sybase ASE server. The program lists all databases and owners who own the procedure or function matching your pattern. Syntax: psFindProcedure -Sserver -Uusername -Ppassword -Ddatabase_pattern -Wproc_func_owner_pattern -Nprocedure_name_pattern -Flog_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database Name Pattern [~tempdb ~model] -W Sybase Procedure/Function Owner Pattern [%] -N Sybase Procedure/Function Name Pattern [%] -F Log File [standard output] psFindProcedure looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:01 psFindProcedure ER00012 You requested the program's help function. --------------------------------------------------------- - - psFindTable - - --------------------------------------------------------- psFindTable: This program finds a table in a Sybase ASE server. The program will list all databases and owners who own the table matching your pattern. Syntax: psFindTable -Sserver -Uusername -Ppassword -Ddatabase_pattern -Wtable_owner_pattern -Ntable_name_pattern -Flog_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database Name Pattern [~tempdb ~model] -W Sybase Table Owner Pattern [%] -N Sybase Table Name Pattern [%] -F Log File [standard output] psFindTable looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:02 psFindTable ER00012 You requested the program's help function. --------------------------------------------------------- - - psFindView - - --------------------------------------------------------- psFindView: This program finds a view in a Sybase ASE server. The program will list all databases and owners who own the view matching your pattern. Syntax: psFindView -Sserver -Uusername -Ppassword -Ddatabase_pattern -Wview_owner_pattern -Nview_name_pattern -Flog_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database Name Pattern [~tempdb ~model] -W Sybase View Owner Pattern [%] -N Sybase View Name Pattern [%] -F Log File [standard output] psFindView looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:02 psFindView ER00012 You requested the program's help function. --------------------------------------------------------- - - psGenerateCCDefines - - --------------------------------------------------------- psGenerateCCDefines: This program writes a C++ header file. The header file will contain a '# define' telling the programmer the byte length of each non-fixed-lengthed column in the database. The names of the '# defines' is TABLE_COLUMN. The names of the '# defines' are always in upper case. Syntax: psGenerateCCDefines -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname Arguments: -S Source Sybase Server Name -U Source Sybase User Name -P Source Sybase User Password -D Source Sybase Database -W Write C++ defines for tables belonging to 'owner' only [-U] -N Write C++ defines for tables having pattern 'name' [!rs%] -F Name of output C++ header file [standard output] psGenerateCCDefines looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:02 psGenerateCCDefines ER00012 You requested the program's help function. --------------------------------------------------------- - - psGenerateDumpName - - --------------------------------------------------------- psGenerateDumpName: This program generates a dump file name. The name consists of the SQL server name, the database name and the current Universal Time (UTC). The name will be sent to standard output if psGenerateDumpName can contact the SQL server and the database. If the SQL Server cannot be contacted or any other error happens, psGenerateDumpName will exit with a non-zero return code. Syntax: psGenerateDumpName -Sserver -Uusername -Ppassword -Ddatabase -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -O Options: t Generate a transaction log dump file name. psGenerateDumpName looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. --------------------------------------------------------- - - psGenerateRIMap - - --------------------------------------------------------- psGenerateRIMap: This program makes a text file of tables and their referential integrity constraints map (RI Map) for input into programs like psGTruncateTable. Syntax: psGenerateRIMap -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Foutput_RI_map -Ooptions Arguments: -S Source Sybase Server Name -U Source Sybase User Name -P Source Sybase User Password -D Source Sybase Database -W Use tables belonging to 'owner' only -N Use tables having pattern 'name' -F Name of output RI map file for psGTruncateTables [standard output] -O Options c Generate the list in order that the tables were created r Generate the list in reverse order that the tables were created i Generate the list with incoming referential integrity constraints psGenerateRIMap looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:03 psGenerateRIMap ER00012 You requested the program's help function. --------------------------------------------------------- - - psGenerateRITriggers - - --------------------------------------------------------- psGenerateRITriggers: This program writes a script which creates update, insert an delete referential integrity triggers for user tables based on keys established by the SQL procedure sp_foreignkey. Optionally, you can choose to use keys defined by sp_commonkey instead. Syntax: psGenerateRITriggers -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -FKorn_script -Rresults_file -Mmax_size_results_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Create triggers for tables owned by 'owner' only -N Create triggers for tables whose name have pattern 'name' [!rs%] -F Name of output UNIX Korn shell script [standard output] -R psSQL results file [standard output] -M Maximum size in bytes of psSQL results file [0] -O Options j Generate triggers based on sp_commonkeys, not sp_foreignkeys psGenerateRITriggers looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:03 psGenerateRITriggers ER00012 You requested the program's help function. --------------------------------------------------------- - - psGenerateSegmentMap - - --------------------------------------------------------- psGenerateSegmentMap: This program makes a table to segment map for input into programs psCopyIndices or psCopyTables. Syntax: psGenerateSegmentMap -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Xtable_segment -xnon_clustered_index_segment -iimage_segment -Foutput_segment_map -Ooptions Arguments: -S Source Sybase Server Name -U Source Sybase User Name -P Source Sybase User Password -D Source Sybase Database -W Use tables belonging to 'owner' only -N Use tables having pattern 'name' -X Target table/clustered index segment -x Target non-clustered index segment -i Image/Text segment -F Name of output segment map file for psCopyTables or psCopyIndices [standard output] -O Options c Generate the list in order that the tables were created r Generate the list in reverse order that the tables were created psGenerateSegmentMap looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:04 psGenerateSegmentMap ER00012 You requested the program's help function. --------------------------------------------------------- - - psGenerateTable - - --------------------------------------------------------- psGenerateTable: This program writes the C++ table subclasses, one for each table in the specified database. Each subclass has at least 6 methods. A constructor, a destructor, and a method each which selects, updates, deletes and inserts a row. Syntax: psGenerateTable -Sserver -Uusername -Ppassword -Ddatabase -Wtable_owner -Nname -Llogfile -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W If -N is specified, then -W specifies the owner of the table. -W defaults to dbo. -N If specified, only the C++ code for tables having pattern 'name' are generated. The default is all tables in the database. -L If specified, all error messages go to this file. If not specified, all error messages go to standard output. psGenerateTable looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:04 psGenerateTable ER00012 You requested the program's help function. --------------------------------------------------------- - - psGetSegment - - --------------------------------------------------------- psGetSegment: This program tells you what segment your table is on. Syntax: psGetSegment -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Ooptions Arguments: -S Source Sybase Server Name -U Source Sybase User Name -P Source Sybase User Password -D Source Sybase Database -W Tables belongs to 'owner' [-U] -N Name of the table -O Options: t Display only the segment name of the table i Display only the segment name of first non-clustered index x Display only the segment name of the image/text chain psGetSegment looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:04 psGetSegment ER00012 You requested the program's help function. --------------------------------------------------------- - - psKillProcesses - - --------------------------------------------------------- psKillProcesses: This process kills user connections to a SQL Server. You can tell whose connections to kill or kill the connections attached to certain databases. Also, you can shutdown the SQL Server in an orderly fashion. You must have sa_role to run this. Syntax: psKillProcesses -Sserver -Uusername -Ppassword -Nlogin_name_pattern -Ddatabase_pattern -Flog_file -Mmax_init_log_size -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User Password -N Select processes with this login name pattern -A Select processes which are running this application pattern -D Select processes which are using databases with this pattern -F Log file -M Maximum initialize size of log file in bytes [0] -O Options b Continue even if Backup Server processes are running d Make sure all user processes are dead t If kill fails, try syb_terminate s Shutdown the server if no processes are left f Shutdown the server immediately the best way possible psKillProcesses looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. PS_SYSMSG_FILE Name of the tool's messages file. Shutting_Down_an_ASE_Server: To make sure a SQL Server is shutdown, just use -Of. A -Of will do: 1. 'kill' on all user processes 2. 'syb_terminate' on any user processes if (1) fails 3. 'shutdown' if there are no user processes left 4. 'checkpoint' in every database and then 'shutdown with nowait' if (3) fails However, remember that I will not kill anything if any of your specified processes are doing dumps or loads. Before interrupting a dump or load, you must first 'shutdown with nowait' the appropriate Backup Servers. I will not do that for you. After you shutdown your Backup Servers, then you can run psKillProcesses. If you want to live dangerously, you can even shutdown the SQL Server while processes are dumping or loading. Do a -Obf. But if you do this, you do it at your own risk. 2009.06.18 21:12:04 psKillProcesses ER00012 You requested the program's help function. --------------------------------------------------------- - - psListDefaults - - --------------------------------------------------------- psListDefaults: This program lists all the defaults in a database that were created using the 'create default' command. Syntax: psListDefaults -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Foutput_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W List defaults belonging to owner only -N List defaults having pattern 'name' -F Name of output file [standard output] psListDefaults looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:05 psListDefaults ER00012 You requested the program's help function. --------------------------------------------------------- - - psListDevices - - --------------------------------------------------------- psListDevices: This program lists all the devices managed by a Sybase ASE database server. You must be the 'sa' to run this program. Syntax: psListDevices -Sserver -Uusername -Ppassword -Foutput_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -F Name of output file [standard output] psListDevices looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified PS_SYSMSG_FILE Name of the tool's messages file Notes: You will run this program often because it is so useful. Run this program when . . . 1. You need to find a device that has unallocated space on it. 2. You need to see all the physical locations of your Sybase logical disk devices. 3. You need to see how the databases are distributed on each physical disk. 4. You want to see which database segments are on what disk fragment. 5. You need to check if your databases' segment maps conflict on a particular disk device. 6. You need to know when your database was created or last dumped. 2009.06.18 21:12:05 psListDevices ER00012 You requested the program's help function. --------------------------------------------------------- - - psListInterfaces - - --------------------------------------------------------- psListInterfaces: This program lists all the servers in the Sybase interfaces file in directory /disk2/ceccareb/PS/os/Solaris/sybaseclient/SparcSol8/r15. Syntax: psListInterfaces-Foutput_file Arguments: -F Name of output file [standard output] psListInterfaces looks for the environment variables: SYBASE Sybase home directory PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:05 psListInterfaces ER00012 You requested the program's help function. --------------------------------------------------------- - - psListProcedures - - --------------------------------------------------------- psListProcedures: This program lists all the stored procedures, extended stored procedures and functions in a database. Syntax: psListProcedures -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Foutput_file -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W List procedures belonging to owner only -N List procedures having pattern 'name' -F Name of output file [standard output] -O Options: d List dynamically prepared procedures only p List the parameters for each procedure r List whether each procedures is read-only s List the permissions on each procedure t Print the text of each procedure k Include comments in report u Report user-defined datatypes. Defaults to native types. psListProcedures looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:06 psListProcedures ER00012 You requested the program's help function. --------------------------------------------------------- - - psListProcesses - - --------------------------------------------------------- psListProcesses: This program lists all the processes (spids) currently running on a Sybase ASE database server. You must be the 'sa' to run this program. You can just list the current processes, or you can sort them by specifying a 'top' number of spids. You can list the top processes by CPU usage, memory usage and physical I/Os. Syntax: psListProcesses -Sserver -Uusername -Ppassword -Lprocess_login_names -Dprocess_databases -Cprocess_client_programs -Tnumber_of_top_processes -Iiterations -Nseconds_between_iterations -Ooptions -Foutput_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -L Sample processes owned by these Sybase login names [%] -D Sample processes using these databases [%] -C Sample processes created by these client programs [%] -T Show only this many top users of the resource [All] -I The number of iterations [1] -N The number of seconds between polling [0] -F Name of output file [standard output] -O Options c CPU m Memory i Physical I/O psListProcesses looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:06 psListProcesses ER00012 You requested the program's help function. --------------------------------------------------------- - - psListRules - - --------------------------------------------------------- psListRules: This program lists all the rules in a database that were created using the 'create rule' command. Syntax: psListRules -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Foutput_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W List rules belonging to owner only -N List rules having pattern 'name' -F Name of output file [standard output] psListRules looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:06 psListRules ER00012 You requested the program's help function. --------------------------------------------------------- - - psListSegments - - --------------------------------------------------------- psListSegments: This program lists all the segments of a database, the objects on the segments, the size of the objects on each segment, the distribution of segments on Sybase logical devices, and the distribution of the objects themselves on Sybase logical devices. You must be 'sa' to run this program. Syntax: psListSegments -Sserver -Uusername -Ppassword -Ddatabase -Ee_mail_address -Tsegment_threshold -Ysybase_execution_priority -Foutput_file -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -E E-mail address to where psListSegments sends notification when any segment's space goes over the threshold -T Threshold. Threshold = (pages used)/(total pages available to segment) -Y Priority ('high', 'medium' or 'low') -F Name of output file [standard output] -O Options s Do NOT report segment total summary. f Do NOT report the disk fragments where the segments reside. t Do NOT report the names of the tables and indices on the disk fragments. d Report the distribution of individual objects on the disk fragments. o Report the OAM structures. psListSegments looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file Notes: A disk fragment equates to a row in the master.dbo.sysusages table. Each time you alter a database, you create a disk fragment. A single logical device may have several fragments. psListSegments finds the location of every data block of a table and index. For every disk fragment, psListSegments sums the data block sizes. Each disk fragment belongs to one or more segments. In this fashion, psListSegments computes the total space allocated for a segment and how much space is left. You can use this information to plan ahead for extending segments and adding new devices for your database. To get an overview of what devices your ASE database server is currently using, run psListDevices. This program is also good for spotting one extremely common DBA blunder and one Sybase white lie. The DBA blunder is this: * A DBA has loaded the database with a dump of the database of a prior fragment or segment configuration. For example, the dump came from the database before it had been 'alter'ed. Or, the dump came from the database when it had 3 segments and the current one as 4. When this happens, this program will report all sorts of crazy numbers. You'll get negative numbers of allocated pages, segment map errors, and pages that do not belong to any segment (non-pool pages). The way out of this mess is to use psCopySchema to copy the data definitions and data from your misconfigured database to a new database. The_White_Lie: * Sybase says, 'When you move a clustered index, the entire table moves with it.' Well, that is not true. You will see that the entire table moves with the clustered index, EXCEPT for the table's initial allocation page. If you move the clustered index from segment A to segment B, you will still find a single allocation page of your table remaining on segment A. Sybase will assign your table and clustered index to segment B, but that one page will be left behind. That one page will not belong to the table's new segment. It will not belong to any particular segment. I call such a page a 'NON-POOL' page. Watch for them. If you want a table to be entirely on a segment, create the table on your desired segment and then create your clustered index on the same desired segment. 2009.06.18 21:12:07 psListSegments ER00012 You requested the program's help function. --------------------------------------------------------- - - psListTables - - --------------------------------------------------------- psListTables: This program lists all the user tables in a database. Syntax: psListTables -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Ttime_out -Anumber_of_rows -Foutput_file -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User Password -D Sybase Database -W List tables belonging to owner only -N List tables having pattern 'name' [%] Example patterns are: employee% %salary% ~exception !f% where % is a wildcard, and ~ or ! mean a 'not' -T Time out in seconds [3600] -A Number of rows to sample to compute average lengths for columns [0] -F Name of output table list file [standard output] -O Options: a List everything n List the caches bound to tables and indexes c List the columns in each table d List the defaults bound to columns R List the rules bound to columns or tables m List the image/text page chain for each table i List the indexes in each table f List the referential integrity constraints k List the sp_primary, sp_common & sp_foreign keys t List the triggers on each table p List the permissions on each table r Count the number of rows for each table x Give exact row counts for each table u Report user-defined datatypes [native datatypes] z Include comments in report s Get the system tables too C List tables in creation order (parent to child) B List tables in reverse creation order (child to parent) psListTables looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:07 psListTables ER00012 You requested the program's help function. --------------------------------------------------------- - - psListTablesForWeb - - --------------------------------------------------------- psListTablesForWeb: This program generates web pages for all the user tables in a database. This programs generates one web page per table plus a single web page called TABLESLIST.html which contains links to all the table web pages. If you do not specify a -O option, then you get all the information for the tables. No -O is equivalent to -O with everything. There is a 'Comments' column for the tables' columns plus an area under each Table Name for comments for the table in general. This program looks for these comments in syscomments. You can easily insert comments into the syscomments table by using the stored procedures in DBPowerSuite/dbaccess/sybase_scripts/add_comment_procs.sql. Syntax: psListTablesForWeb -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -ttime_out -Ttitle -Iimage_of_ERD -Abackground_color -aforeground_color -Llink_color -lviewed_link_color -Kactive_link_color -Cbackground_color_for_odd_rows -cbackground_color_for_even_rows -Foutput_error_log -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User Password -D Sybase Database -W List tables belonging to owner only -N List tables having pattern 'name' [%] Example patterns are: employee% %salary% ~exception !f% where % is a wildcard, and ~ or ! mean a 'not' -t Time out for the database in seconds [3600] -T Title for your table's list [] -I Image of Entity Relationship Diagram [] -A Background color for the page [FFFFFF] -a Foreground color for the page [000084] -L Link color [0000FF] -l Viewed link color [C000C0] -K Active link color [FF0000] -C Color background for odd numbered rows [FFFFFF] -c Color background for even-numbered_rows [F0F0F0] -F Name of output error log [standard output] -O Options: s Get system tables too n List the caches bound to tables and indexes d List the defaults bound to columns R List the rules bound to columns or tables m List the image/text page chain for each table i List the indexes in each table f List the referential integrity constraints k List the sp_primary, sp_common & sp_foriegn keys t List the triggers on each table p List the permissions on each table r Count the number of rows for each table x Give exact row counts for each table u Report user-defined datatypes [native datatypes] q Write fully qualified table names psListTablesForWeb looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:07 psListTablesForWeb ER00012 You requested the program's help function. --------------------------------------------------------- - - psListTriggers - - --------------------------------------------------------- psListTriggers: This program lists all the triggers in a database. Syntax: psListTriggers -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Ttable -Foutput_file -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W List triggers belonging to owner only -N List triggers having pattern 'name' -T List triggers only on table having names patterned after 'table' -F Name of output trigger's list file [standard output] -O Options: l List names of triggers only t Print the text of each trigger psListTriggers looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:08 psListTriggers ER00012 You requested the program's help function. --------------------------------------------------------- - - psListUsers - - --------------------------------------------------------- psListUsers: This program lists all the users in a database and tells you all their personal permissions as well as the permissions they have on the other objects in a database. Syntax: psListUsers -Sserver -Uusername -Ppassword -Ddatabase -Nname -Llogin_name -Foutput_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -N List users having pattern 'name' -L List users having Sybase login 'login_name' -F Name of output file [standard output] psListUsers looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:08 psListUsers ER00012 You requested the program's help function. --------------------------------------------------------- - - psListUserTypes - - --------------------------------------------------------- psListUserTypes: This program lists all the user-defined datatypes in a database. Syntax: psListUserTypes -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Foutput_file Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W List user-defined datatypes belonging to owner only -N List user-defined datatypes having pattern 'name' -F Name of output file [standard output] psListUserTypes looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:08 psListUserTypes ER00012 You requested the program's help function. --------------------------------------------------------- - - psListViews - - --------------------------------------------------------- psListViews: This program lists all the views in a database. Syntax: psListViews -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -Foutput_file -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W List views belonging to owner only -N List views having pattern 'name' -F Name of output file [standard output] -O Options: l List the view names c List the columns for each view p List the permissions for each view t Print the text of each view k Include comments in report u Report user-defined datatypes. Defaults to native types. psListViews looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:09 psListViews ER00012 You requested the program's help function. --------------------------------------------------------- - - psLoadDatabase - - --------------------------------------------------------- psLoadDatabase: This program loads a database. You can load the log from disk or from tape. You can load the database from any Sybase Backup Server so long as the ASE Server knows about the Backup Server. You can load the database in one or more stripes. psLoadDatabase exits with a 0 if your load completed successfully. If Sybase was unable to load the database, then psLoadDatabase exits with a non-zero return code. Whether or not the load was successful, psLoadDatabase writes text messages to your messages log file. If you wish to retrieve the text message associated with the non-zero exit code, then run psShowMessage. Syntax: psLoadDatabase -Sserver -Uusername -Ppassword -Ddatabase -Bbackup_server_name -Xdump_directory -Ldump_file -Rlist_of_stripes_file -Fmessage_log_file -Mmax_msg_log_file_size -Ttime_out -Eemail_address_when_error -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -B Backup Server Name [SYB_BACKUP] -X Database Dump Directory -L Database Dump File Name (If absent, the name will be generated.) -R Input Text File Containing a List of Stripe Files (Specify either both -X and -L, or just -R) -T Time out in seconds [3600 seconds] -F Messages Log File. [standard output] -M Maximum Size of Log File [1,000,000 bytes] (Used only when you specify -F) -E Email address to send errors when there are errors -O Options: t Tape--the -X is the tape drive name r Rewind and unload tape when done h Just list the header on the tape. Do not load. q Query the tape for all dump files. Do not load. l Log full informational messages a Do not log abbreviated informational messages psLoadDatabase looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:09 psLoadDatabase ER00012 You requested the program's help function. --------------------------------------------------------- - - psLoadTransactionLog - - --------------------------------------------------------- psLoadTransactionLog: This program loads a database's transaction log. You can load the log from disk or from tape. You can load the transaction log from any Sybase Backup Server so long as the SQL Server knows about the Backup Server. You can load the log in one or more stripes. psLoadTransactionLog exits with a 0 if your load completed successfully. If Sybase was unable to load the log, then psLoadTransactionLog exits with a non-zero return code. Whether or not the load was successful, psLoadTransactionLog writes text messages to your messages log file. If you wish to retrieve the text message associated with the non-zero exit code, then run psShowMessage. Syntax: psLoadTransactionLog -Sserver -Uusername -Ppassword -Ddatabase -Bbackup_server_name -Xxact_log_directory -Lxact_log_file -Rlist_of_stripes_file -Fmessage_log_file -Mmax_msg_log_file_size -Ttime_out -Eemail_address_when_error -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -B Backup Server Name [SYB_BACKUP] -X Transaction Log Directory -L Transaction Log File Name (If absent, the name will be generated.) -R Input Text File Containing a List of Stripe Files (Specify either both -X and -L, or just -R) -T Time out in seconds. [3600 seconds] -F Messages Log File. [standard output] -M Maximum Size of Log File [1,000,000 bytes] (Used only when you specify -F) -E Email address to send errors when there are errors -O Options: t Tape--the -X is the tape drive name r Rewind and unload tape when done h Just list the header on the tape. Do not load. q Query the tape for all dump files. Do not load. l Log full informational messages a Do not log abbreviated informational messages psLoadTransactionLog looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:09 psLoadTransactionLog ER00012 You requested the program's help function. --------------------------------------------------------- - - psPingServer - - --------------------------------------------------------- psPingServer: This program pings an ASE or Open Server by testing whether a connection can be established. Remember that this program, like any PS program, will exit with a non-zero if it cannot succeed for any reason. Syntax: psPingServer -Sserver -Uusername -Ppassword -Ddatabase -Ttime_to_wait -Ooptions Arguments: -S Sybase Server Name -U Sybase Server User's Name -P Sybase Server User's Password -D Sybase Server Database -T Maximum time to wait for server to be completely up [0 seconds] -O Options: a Tests if user has sa role b Tests if user has sso role c Tests if user has oper role n Tests if user has navigator role r Tests if user has replication role d Test that the database can be used w Test that the database is write-enabled u Test that the user is the database owner t Do NOT test whether all databases are suspect or recovering o Ping an Open Server s Write return code to standard output too e Turn on error messages psPingServer looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. Maximum_Time_for_Server_to_Come_Up: I will continue trying the server every 5 seconds until either the server is up or until the maximum time (-T option) is exceeded. I will always ping the server at least once. If your server is a SQL Server, I also verify that all databases are recovered and not suspect. --------------------------------------------------------- - - psSetPermissions - - --------------------------------------------------------- psSetPermissions: This program takes the tedium out of creating scripts which grant or revoke permissions on groups of objects to groups of users. Given a security configuration file like, /disk2/ceccareb/PS/dbaccess/parameters/security.cfg you can generate thousands of grant and revoke command instantly for thousands of users or groups. Without the security configuration file, you can still generate thousands of grants on all objects of a database to a single user or group. Syntax: psSetPermissions -Sserver -Ulogin_username -Ppassword -Ddatabase -Csecurity_config_file -Ndb_user -Wowner_of_objects -Ooptions -Foutput_script_name Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -C Security Configuration File -N Database User or Group Name -W Owner of Target Objects -F Output Korn Shell script -O Options s Stop if I find an object or user which does not exist in the database. r Revoke permissions first x Grant read permissions on all tables and views. Grant execute on every stored procedure. a Grant all permissions on all objects. This option only used when you do not use -C. Notes: If you do not specify a security configuration file, I look at the -N and -W arguments: I grant 'select' permission on all objects owned by -W to the user -N. I also grant execute permissions on those stored procedures, owned by -W, which only 'select' from tables. (Yes, I know which procs those are.) If you specified the -Oa option, then I grant all permissions on all objects. If specified the -Ox option, then I grant 'select' permissions on tables and views, and execute permissions on all procs. If you specify the -C argument, I read that security configuration file and generate all the grants and revokes implied in the file. You cannot specify -N or -W when you specify -C. If you specify neither -C, -N or -W, then I grant 'select' permissions on all objects owned by 'dbo' to 'public'. I grant execute permission on the stored procs owned by 'dbo' which only select from tables. If you specified the -Ox option, I grant execute permission on all dbo's procs to 'public'. -Or revokes object permissions. You can use -Or with -Oa or -Ox. I will revoke permissions before granting any new ones. You cannot use -Or with -C. The syntax within a security configuration file gives you the ability to revoke permissions with finer granularity. psSetPermissions looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:10 psSetPermissions ER00012 You requested the program's help function. --------------------------------------------------------- - - psSQL - - --------------------------------------------------------- psSQL: This program sends language commands to a Sybase Server. The server can be a Data Server, Backup Server, Replication Server a Net-Gateway or any Open Server. psSQL behaves like isql but has several more features than isql. To run this program interactively, type psSQL -Op at your operating system prompt. The 'p' is for prompt mode. Syntax: psSQL -Sserver -Uusername -Ppassword -Ddatabase -Tquery_results_time_out -tlogin_time_out -Ccommand -Isql_file -Bbatch_terminator -bmax_batch_len -mmax_rcd_len -xmax_image_len -Fresults_file -Rmax_results_file_size -Xcolumn_delimiter_string -Yrow_terminator_string -Lerror_log_file -Mmax_error_log_size -Ee-mail_address -Kkey_word -kkey_word_value -Ooptions Arguments: -S Sybase Server -U Sybase Userame -P Sybase User Password -D Sybase Database -T Maximum Time-out waiting for Results from Sybase [14400 seconds] -t Maximum Time-out waiting for a Connection from Sybase [10 seconds] -C Input single SQL command -I Input SQL command file [standard input] -B Batch terminator [go] -b Maximum batch length [200000] -m Maximum input record length [100000] -x Maximum image length [256] -F Output results file [standard output] -N Null values are displayed with this text [] -X Column delimiter string for output results file fields [|] -Y Row terminator string for output results file [standard OS terminator] -R Maximum starting size of the existing results file [0 bytes] -L Output messages log [output results file] -M Maximum starting size of the existing messages log [0 bytes] -E E-mail address when error -K (upper case K) Keyword or keyword list -k (lower case k) Keyword value or keyword value list -O Options: p Interactive mode P Interactive mode with prompts o Target server is a Replication or Open Server. It is not an ASE Server. x Look for batch terminator at the end of the line. r Discard all results c Do not comment results file i Do not echo your input as comments in the results file t Prefix comments with a time stamp b Remove leading and trailing blanks from results' columns s Put results into vertical columns; do not use the | delimiter e Continue to next batch even when I get an error from Sybase E Always exit with success (0) even when I get an error from Sybase a Do not put abbreviated non-error info messages in the error log l Put full non-error info messages into the error log z Treat server severity 0 as informational as well as severity 10 y Ignore stored procedure creation dependency errors psSQL looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. Exit_Codes: ------------------------------------------------------------------------------ 1. psSQL terminates with a meaningful exit code. If the exit code is 0 then psSQL finished without detecting any errors from the Sybase server. If the exit code is not 0, then Sybase did not like your commands. 2. You can get the text associated with the exit code by running psShowMessage: psShowMessage -Ccode 3. psSQL does understand the difference between errors verses informational messages whether the messages come from SQL Server, Backup Server or Replication Server. If you write your own Open Server, then make 10 your severity level for informational messages and set the severity for an error to anything other than 0 or 10. If you are using the DBPowerSuite Open Server library libpsOpenServer, the library uses severity 10 for information and 100 for an error. Stopping_psSQL_in_the_Middle_of_Your_File: ------------------------------------------------------------------------------ 1. By default, psSQL stops processing your file when it either 1) comes to the end of your file or 2) comes to the end of a batch--Sybase having thown an error during the execution of the batch. (Batches are separated by the word 'go'.) Commands after the bad batch are not sent to the server. 2. You can change this 'stop on error' behavior. By putting -Oe on the command line, psSQL will continue to the next batch in spite of prior errors. Yet still, when psSQL finally ends, it will still end with a non-zero exit code if there were errors. 3. You can conditionally exit your file with an error by coding a 'raiserror' command in your SQL file. 'raiserror' commands force the Sybase data server to throw an error. 4. You can conditionally quit your file in the middle and have psSQL end with a *successful* exit code. You do this with a specific SQL 'print' command: print 'exit psSQL' And at the end of the batch which executes this command, and assuming no prior errors, psSQL will exit successfully. Interactive_Mode: ------------------------------------------------------------------------------ 1. To engage interactive mode, use the -Op option. To see the list of interactive commands native to psSQL, type 'help' at the psSQL prompt. By default, psSQL executes in non-interactive mode. You will not see the psSQL prompt. Also, certain other psSQL-native commands will not be available to you. For example, you can not use the native commands history, recall or clean. Non-Interactive_Modes: ------------------------------------------------------------------------------ 1. You can send a single command to the Sybase server by coding it right on the command line. To do this, use the -C option and enclose your command in quotes. 2. You can send a file of several commands and batches to the Sybase server. Use the -I option along with your file name. 3. If you do not use -C or -I, then psSQL looks for command input from standard input. In this fashion, you can use here-files from within shell scripts or pipe input into psSQL from other programs. SQL,_Backup,_Replication,_LTM,_Net-Gateway_or_Open_Server: ------------------------------------------------------------------------------ 1. By default, psSQL thinks you are connecting to a SQL Server; therefore, the first thing psSQL is going to do is to send the 'use database' command to the server. psSQL gets the name of the database from your -D command line argument. 2. If you are connecting to *anything* that is not a SQL Server, then use -Oo on the command line. This will prevent 'use database' from being sent to the server. Keyword_Substitution: ------------------------------------------------------------------------------ 1. By using the command line arguments -Kold_string and -knew_string, you can make psSQL replace every occurance of old_string in your input commands with new_string. In this way, you can avoid using 'sed' prior to running a SQL script through psSQL. Do not worry, I will not modify your original input file in any way. 2. Actually, old_string can be more than a single key word. Consider this on your command line: -K '_STATE _COUNTRY' -k 'NC USA' psSQL will replace every occurance of _STATE with NC. psSQL will also replace every occurance of _COUNTRY with USA. You can place up to 10 key words next to -K or -k. 3. There are 3 reserved built-in keywords: __DBNAME__, __DATABASE__ and __USERNAME__. Everytime psSQL finds __DBNAME__ or __DATABASE__ in your input, psSQL replaces it with the -D psSQL command line argument. Everytime psSQL finds __USERNAME__ in your input, psSQL replaces it with the -U command line argument. (There are 2 underscores before and after these special keywords.) Results_File: ------------------------------------------------------------------------------ 1. All Sybase servers send psSQL two types of data. The two types of data are 1. Results 2. Messages By default, psSQL sends both kinds of data to the results file. You specify a results file with the -F command line argument. If you not specify the -F your results go to standard output. 2. At this point, let us discuss the differences between 'results' and 'messages' data. Results data is the rows of data produced from select and fetch commands, the return codes from stored procedure and the rows-affected counts from select/update/delete/insert SQL commands. Messages data, on the other hand, is information created by your SQL 'print' commands and 'raiserror' commands. Also, Sybase servers create Messages type data all the time. Everytime a SQL Server changes database it sends Messages type data. Everytime a Sybase Server throws an error, it sends Messages type data. 3. With psSQL, you can split the results data from the messages data. If you specify the -L command line argument, you direct messages to go to a different destination than the results. If you do not specify -L, the messages will go to the same destination as your results. The advantage of having your results and your messages go to the same destination is that you will know what messages go with what results. You will be able to debug your input commands better. The disadvantage is is that message type data is difficult to parse from the surrounding results. I advise that you only separate results from messages when you are certain that Sybase will process all your commands successfully and your results are input to another program. 4. You can tell psSQL to append new results to an existing results file by using the -R option. With the -R, you can specify the maximum size of existing results file. If the existing results file is over this size, psSQL appends a '.old' to the existing file, and then creates a new one for new results to be written into. If the existing results file is less than the -R maximum size, then psSQL just appends its new results to the existing file. 5. Using the 'save' psSQL native command, you can create many results files within a single psSQL session. To see the details of 'save', run psSQL interactively and type 'help'. 6. Discarding results. You can tell psSQL to execute your commands but throw away all results. No results file will be created. Use the -Or to accomplish this. Comments_in_your_Results_File: ------------------------------------------------------------------------------ 1. By default, psSQL will put comments in your results file. Comments take only 2 forms. They either start with the # or they are totally blank lines. 2. If you are going to use the results as input into another program, then you most likely do not want comments in your file. To remove comments, use the -Oc option. If you don't use -Oc, then your program must filter out the comments by using the criteria described in # 1. 3. There may be occasions when you want to time how long your commands take. If you use -Ot, then psSQL will prefix a time stamp to every comment it makes. 4. By using nocomment, comment or timecomment psSQL native commands, you can change the way psSQL comments your results during a session. Column_Delimiters_in_the_Results_File: ------------------------------------------------------------------------------ 1. By default, psSQL delimits every column with the | character in non-interactive mode. 2. By default, psSQL mimics isql in the way it writes results in interactive mode. Columns are separated by varying number of spaces in order to make nice vertical columns. 3. To change the default, you can use the -X command line option. Or you can use the psSQL native command 'delimit'. If you specify a number for the delimiting character, I assume your number is the ASCII decimal equivalent of the desired character. If you specify a letter to begin with, then I use that letter. 4. For BCP operations, | is the definitive delimiting character. Also, it the perfect delimiter if you are programming with DBPowerSuite' UFile class. 5. If you specify -Os on the command line, you get isql-like result output. 'delimit -1' also will get you isql-like output. 6. If your results will be read into Excel, use -X9 as the delimiter. 9 is the ASCII decimal equivalent of a tab. Excel will read such tab-delimited results files directly. Each Sybase column will fall into its own column of Excel cells. Messages_Log_File: ------------------------------------------------------------------------------ 1. By default, Sybase Messages type data will go to the same destination as the results data. Refer to the 'Results File' section. If you declare a -Llog command line argument, then all Sybase's messages will go to file 'log' instead. 2. You can tell psSQL to append new messages to an existing log file by using the -M option. With the -M, you can specify the maximum size of existing messages file. If the existing messages file is over this size, psSQL appends a '.old' to the existing file, and then creates a new one for new messages to be written into. If the existing messages file is less than the -M maximum size, then psSQL just appends its new messages to the existing file. E-mail. You can tell psSQL to e-mail all the error messages to anybody on the internet. If you specify the -E command line argument *and* there are errors (not just information messages, but actual errors), then psSQL will e-mail all the errors when psSQL exits. 2009.06.18 21:12:10 psSQL ER00012 You requested the program's help function. --------------------------------------------------------- - - psTestThreads - - --------------------------------------------------------- psTestThreads: This program exercises the Sybase client libraries. It's purpose is to std::flush out whether the client libraries are thread-safe. If this program cores, then your client libraries are not thread-safe. Syntax: psTestThreads -Sserver -Uusername -Ppassword -Ddatabase -Wwork_table_owner -Itest_table.sql -Tnumber_of_threads -Flog_file Arguments: -S Sybase ASE Server -U Sybase User Name -P Sybase User Password -D Sybase Database -W Owner of Work Tables -I Test Table Creation SQL Script Name [/disk2/ceccareb/PS/dbaccess/sql/sybase/test_table.sql] -T Number of connection threads to Sybase [3] -X Number of times to execute the algorithm on each thread [3] -F Log file psTestThreads looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:11 psTestThreads ER00012 You requested the program's help function. --------------------------------------------------------- - - psTranslateSchema - - --------------------------------------------------------- psTranslateSchema: This program reads a Sybase schema and writes the DDL scripts to create the schema in a different database. For example, it can translate a Sybase schema to an Oracle schema. Syntax: psTranslateSchema -Sserver -Uusername -Ppassword -Ddatabase -Ttime_out -Wowner -Nname -Ltarget_ddl_file -Etarget_ddl_section -Ydatatypes_file -starget_schema_name -wtarget_owner -ptarget_stored_procedure_prefix -dtarget_directory_for_sql_scripts -Flog_file -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User Password -D Sybase Database -W List tables belonging to owner only -N List tables having pattern 'name' [%] Example patterns are: employee% %salary% ~exception !f% where % is a wildcard, and ~ or ! mean a 'not' -T Time out in seconds [3600] -L Target DDL File [/disk2/ceccareb/PS/dbaccess/parameters/ddl.conf] -E Target DDL Section [Oracle 9i] -Y Datatypes File [/disk2/ceccareb/PS/dbaccess/parameters/datatypes.txt] -d Directory Where SQL Scripts Go [.] -s Target Schema Name [schema] (documentation purposes only) -w Target Object Owner [ defaults to original object owner ] -p Target Stored Procedures Prefix [PS_] -F Name of output table list file [standard output] -O Options: u Make all object names upper case l Make all object names lower case w Ignore reserved words g Generate check constraint names a Write all scripts c Write rules (check constraints) r Write RI constraints p Write stored procedures C Add check constraints R Add referential integrity constraints h Write check rules i Write check RI d Write drop rules e Write drop RI D Write drop all objects y Write delete rule violators z Write delete RI violators psTranslateSchema looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:11 psTranslateSchema ER00012 You requested the program's help function. --------------------------------------------------------- - - psTruncateTables - - --------------------------------------------------------- psTruncateTables: This program truncates user tables in a database. System tables are not touched. Before you truncate the tables, you might have to first consider dropping the referential integrity constraints with psDropRIConstraints. Syntax: psTruncateTables -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User Password -D Sybase Database -W Truncate tables belonging to 'owner' only -N Truncate tables having pattern 'name' [!rs%] -F Name of output Korn shell script [standard output] -R psSQL results file name [standard output] -M Initial results file maximum size in bytes [0] psTruncateTables looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:11 psTruncateTables ER00012 You requested the program's help function. --------------------------------------------------------- - - psUnbindDefaults - - --------------------------------------------------------- psUnbindDefaults: This program unbinds table columns from defaults. Only those columns that were bound to defaults created by 'create default' will have sp_unbindefault statements generated. Syntax: psUnbindDefaults -Sserver -Uusername -Ppassword -Ddatabase -Wdefault_owner -Ndefault_name -Vtable_owner -Ttable_name -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Unbind defaults owned by pattern 'default_owner' [-U] -N Unbind defaults named by pattern 'default_name' -V Unbind defaults to those tables belonging to owner only [-U] -T Unbind defaults to those tables named 'name' -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psUnbindDefaults looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:12 psUnbindDefaults ER00012 You requested the program's help function. --------------------------------------------------------- - - psUnbindRules - - --------------------------------------------------------- psUnbindRules: This program unbinds table columns from rules. Only those columns that were bound to rules created by 'create rule' will have sp_unbindrule statements generated. Syntax: psUnbindRules -Sserver -Uusername -Ppassword -Ddatabase -Wdefault_owner -Ndefault_name -Vtable_owner -Ttable_name -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database -W Unbind rules owned by pattern 'rule_owner' [-U] -N Unbind rules named by pattern 'rule_name' -V Unbind rules to those tables belonging to owner only [-U] -T Unbind rules to those tables named 'name' -F Name of output Korn shell script [standard output] -R Results file for psSQL [standard output] -M Maximum size of results file [0] psUnbindRules looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. SYB_DATABASE When -D is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:12 psUnbindRules ER00012 You requested the program's help function. --------------------------------------------------------- - - psUpdateServer - - --------------------------------------------------------- psUpdateServer: This program writes a script which updates the statistics of the tables in every database in a Sybase Adaptive Server. By default, this program updates statistics in all databases except for tempdb and model. As in all DBPowerSuite, this program and its generated script will exit with a 0 if all the update statistics commands executed successfully. However, note that if you use the -Ox option, you will not be able to retrieve the exit code of the generated script. Syntax: psUpdateServer -Sserver -Uusername -Ppassword -Ddatabase_pattern -FKorn_script -Rresults_file -Mmax_results_file_size -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User's Password -D Sybase Database Name Pattern [~tempdb ~model] -F Korn Shell Script Name -R psSQL Results File Name. [standard output] -M psSQL Maximum Size of Results File [1,000,000 bytes] (Used only when you specify -F) -O Options: s Update statistics for system tables too x Execute script immediately [-F is required] psUpdateServer looks for the environment variables: DSQUERY When -S is not specified. SYB_USER When -U is not specified. SYB_PASS When -P is not specified. PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:12 psUpdateServer ER00012 You requested the program's help function. --------------------------------------------------------- - - psUpdateStatistics - - --------------------------------------------------------- psUpdateStatistics: This program writes a Korn shell which contains SQL commands to update statistics and sp_recompile tables in a database. You should run this program and script regularly when changes are occurring in your tables. Syntax: psUpdateStatistics -Sserver -Uusername -Ppassword -Ddatabase -Wowner -Nname -FKorn_script -Rresults_file -Mresults_file_max_size -Ooptions Arguments: -S Sybase Server Name -U Sybase User Name -P Sybase User Password -D Sybase Database -W Update statistics on tables belonging to 'owner' only -N Update statistics on tables having pattern 'name' -F Name of output Korn shell script [standard output] -R psSQL results file name [standard output] -M psSQL initial max size in bytes for results file [0] -O Options: s Update statistics for system tables too a Update 'all' statistics x Execute the script immediately psUpdateStatistics looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:13 psUpdateStatistics ER00012 You requested the program's help function. ######################################################### # # # Sybase Replication Server Programs # # # ######################################################### --------------------------------------------------------- - - psCheckRepDefs - - --------------------------------------------------------- psCheckRepDefs: This program checks and waits for your replication definitions to attain your specified state. The database you specify below (-D) is the RSSD database where your replication definitions reside. You also can specify a file name containing a list of rep defs. If you do this, then you cannot use the -N, -B, -s, -d, -A, -C or -T arguments. Syntax: psCheckRepDefs -Srssd_server -Urssd_username -Prssd_password -Drssd_database -Lrep_def_list_file -Nrep_def_names -Brep_server_name -APDS -CPDS_database -Ttable_names -Flog -Mmax_initial_size_of_log -tMax_time_to_attain_state -Ostate_to_attain Arguments: -S Data Server Name -U Data Server User -P Data Server Password -D Database Name Constraining Arguments: -L List of Replication Definition Names -N Replication Definition Name Pattern -B Replication Server Name Pattern -A Primary Data Server (containing primary table) Name Pattern -C Primary Database (containing primary table) Name Pattern -T Primary Table Name Pattern Output Arguments: -F Name of message log [standard output] -M Maximum initial size of message log [0 bytes] Check Replication Definition State Arguments: -t Maximum time to attain state [60 seconds] -O State to Attain: d Rep Def defined x Rep Def dropped psCheckRepDefs looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:13 psCheckRepDefs ER00012 You requested the program's help function. --------------------------------------------------------- - - psCheckSubscriptions - - --------------------------------------------------------- psCheckSubscriptions: This program checks and waits for your subscriptions to attain your specified state. The database you specify below (-D) is the RSSD database where your subscriptions reside. You also can specify a file name containing a list of subscriptions. If you do this, then you cannot use the -N, -B, -s, -d, -A, -C or -T arguments. Syntax: psCheckSubscriptions -Srssd_server -Urssd_username -Prssd_password -Drssd_database -Lsubscription_list_file -Nsubscription_names -Brep_def_names -sRDS -dRDS_database -APDS -CPDS_database -Ttable_names -Flog -Mmax_initial_size_of_log -tMax_time_to_attain_state -Ostate_to_attain Arguments: Data Server having the RSSD: -S Data Server Name -U Data Server User -P Data Server Password -D Database Name Constraining Arguments: -L List of subscription names -N Subscription Name Pattern -B Replication Definition Name Pattern -s Replicate Data Server (containing replicate table) Name Pattern -d Replicate Database (containing replicate table) Name Pattern -A Primary Data Server (containing primary table) Name Pattern -C Primary Database (containing primary table) Name Pattern -T Primary Table Name Pattern Output Arguments: -F Name of message log [standard output] -M Maximum initial size of message log [0 bytes] Check Subscription State Arguments: -t Maximum time to attain state [60 seconds] -O State to Attain: d Subscription defined a Subscription activated v Subscription validated x Subscription dropped p Check subscription at the primary site [defaults to replicate site] psCheckSubscriptions looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:13 psCheckSubscriptions ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropRepDefs - - --------------------------------------------------------- psDropRepDefs: This program writes a Korn shell script. The script logs into the Primary Replication Server and drops replication definitions. The reason you do not specify the RSSD name nor the Data Server where the RSSD lives is because I get this information from from the Replication Server. You just have to make sure that the user and password you supply are for the Data Server I find. Syntax: psDropRepDefs -Sprs_server -Uprs_username -Pprs_password -uRSSD_username -pRSSD_password -Lrep_def_list_file -Adata_server_pattern -Cdatabase_pattern -Brep_def_names -Ntable_names -Tmax_drop_time -Grds -Hrds_user -Jrds_password -Krds_rssd -FKorn_script -Rresults_file -Mresults_file_max_size Arguments: Primary Replication Server: -S Primary Replication Server Name -U Primary Replication Server User -P Primary Replication Server Password Data Server containing the RSSD: -u Data Server User -p Data Server Password Constraining Arguments: -L Rep Def List File -A Data Server pattern. Only drop rep defs on tables from these data servers. -C Database pattern. Only drop rep defs on tables in these databases. -B Replication Definition Name Pattern -N Mark tables having pattern 'name' [!rs%] -T Maximum time (in seconds) allowed for rep defs to drop from the RSSD [60] Optional Replicate RSSD Data Server: -G Replicate Data Server Name -H Replicate Data Server User [-u] -J Replicate Data Server Password [-p] -K Replicate Data Server RSSD Output Files: -F Name of output Korn shell script [standard output] -R psSQL results file name [standard output] -M psSQL initial maximum size in bytes for results file [0] psDropRepDefs looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:14 psDropRepDefs ER00012 You requested the program's help function. --------------------------------------------------------- - - psDropSubscriptions - - --------------------------------------------------------- psDropSubscriptions: This program creates a script which drops subscriptions. The script first logs into the Replicate Replication Server and issues drop subscription commands. Afterwards, it calls psCheckSubscriptions to make sure they are dropped. Syntax: psDropSubscriptions -Srds -Urds_user -Prds_password -Lsubscription_list_file -Nsubscription_name_pattern -Brep_def_name_pattern -Drds_database_pattern -Apds_pattern -Cprimary_db -Tprimary_table_name_pattern -sPDS_RSSD -uPDS_RSSD_user -pPDS_RSSD_password -dPDS_RSSD_database -Grrs -Hrrs_user -Jrrs_password -Fkorn_script -Rresults_file -Mmax_init_results_size -Ooptions -Qargument_input_file Arguments: Replicate Data Server: -S Data Server Name -U Data Server User -P Data Server Password Drop Subscriptions according to . . . -L Subscription List File -N Subscription Name Pattern [%] -D Replicate Database Name Pattern [%] -B Replication Definition Name Pattern [%] -A Primary Data Server Name Pattern [%] -C Primary Database Name Pattern [%] -T Primary Table name pattern 'name' [!rs%] Primary RSSD Site: -s Data Server [-S] -u Data Server Username [-U] -p Data Server Password [-P] -d Primary RSSD Database [-D] Replicate Rep Server: -G Replicate Replication Server -H Replicate Replication Server Username [-u] -J Replicate Replication Server Password [-p] -t Maximum Time for Subscription To Dematerialize and Drop [60] seconds Output Files: -F Korn Shell to Drop Subscriptions [standard output] -R psSQL Results File [standard output] -M psSQL Initial Maximum Size of Results File [0] You Can Put All Arguments into an Argument File with . . . -Q Argument Input File Options: -O p Drop with Purge (default is without purge) s Purge with suspension r Purge with Suspension at Active Replicate Only i Purge Incrementally psDropSubscriptions looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:14 psDropSubscriptions ER00012 You requested the program's help function. --------------------------------------------------------- - - psGenerateRepDefs - - --------------------------------------------------------- psGenerateRepDefs: This program reads table definitions from the Primary Data Server and writes a Korn shell script containing Replication Server create replication definition commands. The generated Korn script will execute two psSQL sessions. One with the Replication Server and the other with the Primary Data Server. Syntax: psGenerateRepDefs -Spds -Upds_username -Ppds_password -Dpds_database -Wowner -Nname -srep_server -urep_user -prep_pass -Arep_def_prefix -Tmax_time_for_rssd -Lrep_def_list_file -FKorn_shell_script -Rresults_file -Mresults_file_max_size -Grds -Hrds_user -Jrds_password -Krds_rssd -Ooptions Arguments: -S Primary Data Server Name -U Primary Data Server User Name -P Primary Data Server User Password -D Primary Data Server Database -W Make replication definitions for tables belonging to 'owner' [-U] -N Make replication definitions for tables having pattern 'name' [!rs%] -s Replication Server Name [-S] -u Replication Server User Name [-U] -p Replication Server User Password [-P] -A Prefix to add to replication definition names -T Maximum time (in seconds) allowed for rep defs to appear in RSSD [60] -L Output Rep Def List File -F Name of output Korn shell script [standard output] -R psSQL results file name [standard output] -M psSQL initial maximum size in bytes for results file [0] Optional Replicate RSSD: -G Replicate RSSD Data Server Name -H Replicate RSSD Data Server User [-U] -J Replicate RSSD Data Server Password [-P] -K Replicate RSSD Name -O Options: n PDS tables do not have the same names as the corresponding RDSs' tables c All possible columns are searchable by subscriptions m Do not replicate the minimum number of columns 1 Send standby just replication-defined columns 2 Send standby all columns r Turn replication on. This works when the RSSD resides on the Primary Data Server. psGenerateRepDefs looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:14 psGenerateRepDefs ER00012 You requested the program's help function. --------------------------------------------------------- - - psGenerateSubscriptions - - --------------------------------------------------------- psGenerateSubscriptions: This program generates subscriptions by using replication definitions. It also generates the code to activate and validate the subscriptions. If you do not use -Oa or -On, this program assumes you are doing bulk materialization. If you are doing bulk materialization, you have the option to not validate the subscriptions after they are activated (-Ov). If you specify a -V option, this program will produce a separate Korn shell script which just validates the subscriptions Syntax: psGenerateSubscriptions -Spds -Upds_user -Ppds_password -Dpds_rssd_database -Arep_def_ds_pattern -Crep_def_db_pattern -Brep_def_name -Ntable_name -sRDS -uRSD_user -pRDS_password -dRDS_target_database -Grrs -Hrrs_user -Jrrs_password -Lsubs_list -Fkorn_script -Vvalidation_korn_script -Rresults_file -Mmax_init_results_size -Ooptions -Qargument_input_file Arguments: Primary Data Server having the RSSD: -S Data Server Name -U Data Server User -P Data Server Password -D RSSD Database Name Generate Subscriptions only for Rep Defs Matching . . . -A Primary Replication Server Name Pattern [%] -B Replication Definition Name Pattern [%] -C Data Server Name Pattern [%] -c Database Name Pattern [%] -N Table name pattern 'name' [!rs%] Replicate Site: -s Replicate Data Server [-S] -u Replicate Data Server Username [-U] -p Replicate Data Server Password [-P] -d Replicate Target Database [-D] -G Replicate Replication Server -H Replicate Replication Server Username [-u] -J Replicate Replication Server Password [-p] -T Maximum Time for Subscription To Reach State [60] seconds Output Files: -L List of Subscriptions File (for input to psCheckSubscriptions) -F Korn Shell to Create Subscriptions [standard output] -V Korn Shell To Validate Subscriptions [appended to -F] -R psSQL Results File [standard output] -M psSQL Initial Maximum Size of Results File [0] You Can Put All Arguments into an Argument File with . . . -Q Argument Input File Option Argument: -O a Atomic Materialization n Non-atomic Materialization s Activate with Suspension r Activate with Suspension at Replicate Only v Do NOT validate psGenerateSubscriptions looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:15 psGenerateSubscriptions ER00012 You requested the program's help function. --------------------------------------------------------- - - psListRepDefs - - --------------------------------------------------------- psListRepDefs: This program lists your replication definitions. The database you specify below is the RSSD database where your replication definitions reside. Syntax: psListRepDefs -Srssd_server -Urssd_username -Prssd_password -Drssd_database -sPDS -dPDS_database -Aprimary_rep_server -Brep_def_names -Ntable_names -Freport -Rresults_file -Mresults_file_max_size -Ooptions Arguments: Data Server having the RSSD: -S Data Server Name -U Data Server User -P Data Server Password -D Database Name Constraining Arguments: -s Primary Data Server Name Pattern [%] -d Primary Data Server database pattern [%] -A Primary Replication Server Name Pattern [%] -B Replication Definition Name Pattern [%] -N Table name pattern 'name' [%] Output File: -F Report containing list of replicaton definitions [standard output] Option Argument: -O c Do not display rep def columns. psListRepDefs looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:15 psListRepDefs ER00012 You requested the program's help function. --------------------------------------------------------- - - psListSubscriptions - - --------------------------------------------------------- psListSubscriptions: This program lists your subscriptions. The database you specify below (-D) is the RSSD database where your subscriptions reside. Syntax: psListSubscriptions -Srssd_server -Urssd_username -Prssd_password -Drssd_database -Nsubscription_names -Brep_def_names -sRDS -dRDS_database -APDS -CPDS_database -Ttable_names -Fsubscriptions_report Arguments: Data Server having the RSSD: -S Data Server (containing the RSSD) Name -U Data Server User -P Data Server Password -D Database Name Constraining Arguments: -N Subscription Name Pattern -B Replication Definition Name Pattern -s Replicate Data Server (containing replicate table) Name Pattern -d Replicate Database (containing replicate table) Name Pattern -A Primary Data Server (containing primary table) Name Pattern -C Primary Database (containing primary table) Name Pattern -T Primary Table Name Pattern Output Argument: -F Report containing list of subscriptions [standard output] psListSubscriptions looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:15 psListSubscriptions ER00012 You requested the program's help function. --------------------------------------------------------- - - psMarkTablesForRep - - --------------------------------------------------------- psMarkTablesForRep: This program marks tables for replication. The database you specify below is the RSSD database where your replication definitions reside. Syntax: psMarkTablesForRep -Srssd_server -Urssd_username -Prssd_password -Drssd_database -sPDS -uPDS_username -pPDS_password -dPDS_database -Aprimary_rep_server -Brep_def_names -Ntable_names -FKorn_script -Rresults_file -Mresults_file_max_size -Ooptions Arguments: Data Server having the RSSD: -S Data Server (having the RSSD) Name -U Data Server User -P Data Server Password -D Database Name Primary Data Server (PDS) containing the Tables: -s PDS Name [RSSD Data Server Name] -u PDS User [RSSD Data Server User] -p PDS Password [RSSD Password] -d PDS Database Name Pattern [%] Constraining Arguments: -A Primary Replication Server Name Pattern [%] -B Replication Definition Name Pattern [%] -N Mark tables having pattern 'name' [!rs%] Output Files: -F Name of output Korn shell script [standard output] -R psSQL results file name [standard output] -M psSQL initial max size in bytes for results file [0] -O Options: i Do not replicate images and text types o Turn replication off psMarkTablesForRep looks for the environment variables: DSQUERY When -S is not specified SYB_USER When -U is not specified SYB_PASS When -P is not specified SYB_DATABASE When -D is not specified PS_SYSMSG_FILE Name of the tool's messages file 2009.06.18 21:12:15 psMarkTablesForRep ER00012 You requested the program's help function. ######################################################### # # # Sybase Open Server Programs # # # ######################################################### --------------------------------------------------------- - - psOpenServer This program is not supported with this package. - - --------------------------------------------------------- ######################################################### # # # Utilities # # # ######################################################### --------------------------------------------------------- - - psArchiveFile - - --------------------------------------------------------- psArchiveFile: This program renames a to if file is larger than bytes. Syntax: psArchiveFile -Nfile -Msize -Flog_file Arguments: -N File to Archive -M The Maximum Size of the File in Bytes [1000000] -F Output log of messages [standard output] psArchiveFile looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:16 psArchiveFile ER00012 You requested the program's help function. --------------------------------------------------------- - - psCase - - --------------------------------------------------------- psCase: This program converts a phrase to upper or lower case. Syntax: psCase -Uphrase -Lphrase -Foutput_file Arguments: -U If present, converts phrase to upper case -L If present, converts phrase to lower case -F Name of file to write results [standard output] psCase looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:16 psCase ER00012 You requested the program's help function. --------------------------------------------------------- - - psCompute - - --------------------------------------------------------- psCompute: This program evaluates a mathematical expression. An example of an expression is (5 * 6) - 1 * tan(30). Syntax: psCompute -Iinput_expression -Foutput_file Arguments: -I Expression -F Name of file to write results [standard output] psCompute looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:16 psCompute ER00012 You requested the program's help function. --------------------------------------------------------- - - psCopyFile - - --------------------------------------------------------- psCopyFile: This program copies a source file to a target file. This program is similar to the 'cp' or 'copy' operating system command, except that this program is not so easily thwarted by incoming signals, and that this program return standard DBPowerSuite errors and messages. Syntax: psCopyFile -Ssource_file -Ttarget_file -Flog_file Arguments: -S Source File Name [REQUIRED] -T Target File Name [REQUIRED] -F Output log of messages [standard output] psCopyFile looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:16 psCopyFile ER00012 You requested the program's help function. --------------------------------------------------------- - - psControlCharacters - - --------------------------------------------------------- psControlCharacters: This program shows all the control characters in a file. Syntax: psControlCharacters -Iinput_file -Mmax_lines -Nshow_lines -Foutput_file -Ooptions Arguments: -I Input Text File [standard input] -M Analyze up to this many lines -N Show Every 'N'th Line -F Output Report [standard output] -O Options t Show tabs l Show line feeds r Do not show carriage returns a Show all characters psControlCharacters looks for the environment variables: PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:16 psControlCharacters ER00012 You requested the program's help function. --------------------------------------------------------- - - psConvertFileToLowerCase - - --------------------------------------------------------- psConvertFileToLowerCase: This program converts file to upper case. Syntax: psConvertFileToLowerCase -Iinput_file -Foutput_file Arguments: -I Input Text File [standard input] -F Output Text File [standard output] psConvertFileToLowerCase looks for the environment variables: PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:17 psConvertFileToLowerCase ER00012 You requested the program's help function. --------------------------------------------------------- - - psConvertFileToUpperCase - - --------------------------------------------------------- psConvertFileToUpperCase: This program converts file to upper case. Syntax: psConvertFileToUpperCase -Iinput_file -Foutput_file Arguments: -I Input Text File [standard input] -F Output Text File [standard output] psConvertFileToUpperCase looks for the environment variables: PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:17 psConvertFileToUpperCase ER00012 You requested the program's help function. --------------------------------------------------------- - - psConvertToWebText - - --------------------------------------------------------- psConvertToWebText: This program reads a text file and writes its new HTML file equivalent. Simply put, this program converts all leading spaces to   and ends each line with a
. Syntax: psConvertToWebText -Iinput_file -Nnew_HTML_file -Tspaces_for_tab -Flog_file Arguments: -I Name of input file -N Name of new HTML file [-I] -T Number of spaces for a tab [4] -F Output log of messages [standard output] -O Options f Use a fixed space font psConvertToWebText looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:17 psConvertToWebText ER00012 You requested the program's help function. --------------------------------------------------------- - - psDecode - - --------------------------------------------------------- psDecode: This program decodes your text and shows you the bit pattern that it represents. Syntax: psDecode -Ttext -Foutput_file Arguments: -T Your encoded text -F Name of file to write results [standard output] psDecode looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:17 psDecode ER00012 You requested the program's help function. --------------------------------------------------------- - - psDeleteFiles - - --------------------------------------------------------- psDeleteFiles: This program deletes selected files recursively under a directory leaving the subdirectory tree structure in tact. Syntax: psDeleteFiles -Ddirectory -Nfile_name -Flog_file -Ooptions Arguments: -D Name of Directory [REQUIRED] -N File Name Pattern [*] -F Output log of messages [standard output] Example: -------------- psDeleteFiles -D/home/app/src -Ncore This deletes all the core files from under /home/app/src. psDeleteFiles looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:17 psDeleteFiles ER00012 You requested the program's help function. --------------------------------------------------------- - - psDemangle - - --------------------------------------------------------- psDemangle: This program demangles a C++ symbol. Syntax: psDemangle -Smangled_symbol -Foutput_file Arguments: -S The C++ mangled symbol -F Name of file to write results [standard output] psDemangle looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:18 psDemangle ER00012 You requested the program's help function. --------------------------------------------------------- - - psDocumentAPI - - --------------------------------------------------------- psDocumentAPI: This program generates web-pages that describe how to use an API. This program reads an API Definition File and all C++ source code that the API Definition File points to. It organizes all this information and the writes web pages describing all the API's classes and functions. Syntax: psDocumentAPI -Iapi_definition_file -Bbase_dir_for_files_in_api_def_file -Doutput_web_page_directory -Abackground_color -aforeground_color -Llink_color -lviewed_link_color -Kactive_link_color -Cbackground_color_for_odd_rows -cbackground_color_for_even_rows -Foutput_log_file -Ooptions Arguments: -I Input API Definition File [/disk2/ceccareb/PS/package/parameters/PublicAPI.txt] -B Base directory for the API Definition File paths [/disk2/ceccareb/PS] -D Directory to write all the generated web pages [.] -X Checkmark GIF File relative to -D [./images/checkmark.gif] -P One Pixel GIF File relative to -D [./images/onepixel.gif] -R Architecture Jpeg File relative to -D [./images/PSArch.jpg] -A Background color for the page [FFFFFF] -a Foreground color for the page [000084] -L Link color [0000FF] -l Viewed link color [C000C0] -K Active link color [FF0000] -C Color background for odd numbered rows [FFFFFF] -c Color background for even-numbered_rows [F0F0F0] -F Output log file [standard output] -O Options s Do NOT sort functions alphabetically (as opposed to the order in the header files). v Verbose. Write lots of progress messages to the log. psDocumentAPI looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. Notes: The top most web page which psDocumentAPI generates is called API.html. API.html will be in the -D directory. From API.html, a web browser will be able to traverse the entire web page structure. 2009.06.18 21:12:18 psDocumentAPI ER00012 You requested the program's help function. --------------------------------------------------------- - - psDocumentPrograms - - --------------------------------------------------------- psDocumentPrograms: This program generates a help web page for each PS program. This program also generates one web page which is a table of contents which contains links to all the other web pages. This program uses psHelp and the '-help' option of every PS program to generate the pages. If you looking at a web page right now, you are looking at the output of psDocumentPrograms. Syntax: psDocumentPrograms -Pprogram_list_file -Dweb_page_directory -Abackground_color -aforeground_color -Llink_color -lviewed_link_color -Kactive_link_color -Cbackground_color_for_odd_rows -cbackground_color_for_even_rows -Ttitle -Hoverall_help_program -pprogram_prefix -Foutput_error_log Arguments: -P Program List File [/disk2/ceccareb/PS/PROGRAMLIST.txt] -D Directory to Write Web Pages [.] -F Output Log [standard output] -A Background color for the page [FFFFFF] -a Foreground color for the page [000084] -L Link color [0000FF] -l Viewed link color [C000C0] -K Active link color [FF0000] -C Color background for odd numbered rows [FFFFFF] -c Color background for even-numbered_rows [F0F0F0] -T Title [DBPowerSuite 7.9 List of Programs] -H Help program [psHelp] -p Program prefix [ps] psDocumentPrograms looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:18 psDocumentPrograms ER00012 You requested the program's help function. --------------------------------------------------------- - - psDoesExpressionMatch - - --------------------------------------------------------- psDoesExpressionMatch: This program tests a LIKE expression. Syntax: psDoesExpressionMatch -Eexpression -Iinput_string -Foutput_file Arguments: -E Expression [blue%] -I Input Test String [bluegreen] -F Name of file to write results [standard output] psDoesExpressionMatch looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:18 psDoesExpressionMatch ER00012 You requested the program's help function. --------------------------------------------------------- - - psDoesProcessExist - - --------------------------------------------------------- psDoesProcessExist: This program tells you whether a process exists or not. The program writes a 1 (exists) or 0 (does not exist) to your output file depending on what process ID you pass in with the -I argument. Syntax: psDoesProcessExist -Iprocess_id -Foutput_file Arguments: -I Process ID -F Name of file to write results [standard output] psDoesProcessExist looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:19 psDoesProcessExist ER00012 You requested the program's help function. --------------------------------------------------------- - - psGetComputerName - - --------------------------------------------------------- psGetComputerName: This program gets the name of a computer given the the IP address or network address. Syntax: psGetComputerName -Icomputer_name -Foutput_file Arguments: -I IP address [10.0.0.88] -F Name of file to write results [standard output] psGetComputerName looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:19 psGetComputerName ER00012 You requested the program's help function. --------------------------------------------------------- - - psGetIPAddress - - --------------------------------------------------------- psGetIPAddress: This program gets the IP and network address of a computer. Syntax: psGetIPAddress -Icomputer_name -Foutput_file Arguments: -I Computer Name [firstsun] -F Name of file to write results [standard output] psGetIPAddress looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:19 psGetIPAddress ER00012 You requested the program's help function. --------------------------------------------------------- - - psHash - - --------------------------------------------------------- psHash: This program shows you the 3 pairs of hash values for your text. The numbers in a pair have the same internal binary pattern. I am expressing one as signed and the other unsigned. Each pair, though, I compute from a different algorithm. The first 2 pairs produce a 32 bit hash. The 3rd pair is a 64-bit hash. Syntax: psHash -Ttext -Foutput_file Arguments: -T Your text -F Name of file to write results [standard output] psHash looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:19 psHash ER00012 You requested the program's help function. --------------------------------------------------------- - - psHelp - - --------------------------------------------------------- psHelp: This program lists all the programs in DBPowerSuite. It also shows you the current values of important environment variables like PATH, LD_LIBRARY_PATH, SYBASE, ORACLE_HOME . . . Syntax: psHelp -Flog_file Arguments: -F Output log [standard output] psHelp looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:19 psHelp ER00012 You requested the program's help function. --------------------------------------------------------- - - psListFiles - - --------------------------------------------------------- psListFiles: This program is similar to 'ls'. The purpose of the program is to show the application programmer exactly what information is available to him in the UTL_FILES_LIST structure. Syntax: psListFiles -Ndirectory -Flog_file Arguments: -N Name of Directory [current directory] -P Name Pattern [*] -F Output list file [standard output] -O Options d Include all directories psListFiles looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:19 psListFiles ER00012 You requested the program's help function. --------------------------------------------------------- - - psListParameters - - --------------------------------------------------------- psListParameters: This program lists all the parameters in the parameters file. Syntax: psListParameters -Pparameters_file -Foutput_text_file Arguments: -P Name of parameters index file -F Name of output parameter list file. Defaults to standard output. psListParameters looks for the environment variables: PS_SYSPARAM_FILE When -P is not specified. PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:20 psListParameters ER00012 You requested the program's help function. --------------------------------------------------------- - - psLoadMessages - - --------------------------------------------------------- psLoadMessages: This program creates a UIndexFile and an editable XPG4 messages file from a DBPowerSuite standard text file. Once you run gencat on the XPG4 file, the resulting catalog file is a standard UNIX international messages catalog. Do NOT specify the extensions on your file names. Syntax: psLoadMessages -Istandard_text_file_input -Xxpg4_messages_output -Nxpg4_set_number -Mindex_file_output -Ooptions -Flog_file Arguments: -I Input Standard Text File [/disk2/ceccareb/PS/utilities/parameters/messages.txt] -X Output XPG4 Editable Text File [-I + .xpg] -N XPG4 Set Number (1 <= n <= 255) [240] -M Output UIndexFile [-I + UIndexFile extensions] -F Log File [standard output] -O Options -x Do NOT write XPG4 file -m Do NOT write UIndexFile psLoadMessages looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:20 psLoadMessages ER00012 You requested the program's help function. --------------------------------------------------------- - - psLoadParameters - - --------------------------------------------------------- psLoadParameters: This program creates a UIndexFile parameters file from a DBPowerSuite standard text file. Do NOT specify the extensions on your file names. Syntax: psLoadParameters -Istandard_text_file_input -Pparameters_index_file_output -Flog_file Arguments: -I Input Standard Text File [/disk2/ceccareb/PS/utilities/parameters/parameters.txt] -P Output Parameters UIndexFile Parameters [-I + UIndexFile extensions] -F Log File [standard output] psLoadParameters looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. PS_SYSPARAM_FILE For the location of the system-wide parameters file. 2009.06.18 21:12:20 psLoadParameters ER00012 You requested the program's help function. --------------------------------------------------------- - - psMakeMessagesFunction - - --------------------------------------------------------- psMakeMessagesFunction: This program makes a C++ function called uGetMessage.cc. uGetMessage() has an in-memory list of DBPowerSuite messages. You can pass uGetMessage a standard DBPowerSuite code and the code's associated message. uGetMessage is a convenient function for applications that do not package the DBPowerSuite standard messages file. Syntax: psMakeMessagesFunction -Mmessages_file -Foutput_CC_file Arguments: -M Name of a messages index file -F Name of output function file. standard output. psMakeMessagesFunction looks for the environment variables: PS_SYSPARAM_FILE When -P is not specified. --------------------------------------------------------- - - psPortCode - - --------------------------------------------------------- psPortCode: This program generates a Korn shell script. The Korn shell script recursively copies files from one directory structure to another. It is like 'cp -R' is this respect. In addition, the script will change the name of a file substituting a new string for each instance it finds an old string in the name. The script also replaces the same occurances of the old string with the new string in text files. This program deems a file a text file if it has an ending which matches one of the 'text file endings' below. Syntax: psPortCode -Dtemplate_directory -dnew_directory -Sold_string -snew_string -Pstring_pairs_file -Ttext_file_endings -Fscript_name -Ooptions Arguments: -D Name of template directory [current directory] -d Name of new empty directory -S Old string [Linux] -s New string -P Old string-new string pairs file -T Text file endings [.c .cc .cpp .h .hpp .mh Makefile .csh .ksh .sh .txt .list] -F Output Korn shell script [standard output] -O Options l Convert lower case equivalents too u Convert upper case equivalents too psPortCode looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. Notes: If you specify a string-pairs file, this program also replaces strings within your text files according to the old and new values specified in the file. The names of the files themselves, gets changed according only to the -S and -s arguments and whether you specify -Ou, -Ol or -Olu. To replace the lower case equivalents of your strings (-S) within your text files, specific -Ol. For upper case, -Ou. -Olu replaces both the lower and upper case equivalents of your strings within text files. We use this program to begin porting PS from one platform to another or when replacing strings in the entire system. For example, we want to use the PS/os/Linux directory as a template for the code base of HPUX. This program generates the script to copy all the Linux files to the HPUX directory. Any file name that has 'Linux' it in, this program changes 'Linux' to 'HPUX'. Any 'Linux' found in .cc, .h and Makefile this program replaces with 'HPUX'. 2009.06.18 21:12:20 psPortCode ER00012 You requested the program's help function. --------------------------------------------------------- - - psRemoveWhiteSpaces - - --------------------------------------------------------- psRemoveWhiteSpaces: This program removes extra white spaces from a text file. Syntax: psRemoveWhiteSpaces -Iinput_file -Foutput_file Arguments: -I Input Text File [standard input] -F Output Text File [standard output] psRemoveWhiteSpaces looks for the environment variables: PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:21 psRemoveWhiteSpaces ER00012 You requested the program's help function. --------------------------------------------------------- - - psRenameFile - - --------------------------------------------------------- psRenameFile: This program renames a source file to a target file. This program is similar to the 'mv' or 'rename' operating system command, except that this program is not so easily thwarted by incoming signals or 0 hard link numbers on files. If there is a 0 hard link problem, this program copies the old file to the new. If the source file has the 0 hard link problem, then this program truncates the old file since a delete is not possible. Syntax: psRenameFile -Ssource_file -Ttarget_file -Flog_file Arguments: -S Source File Name [REQUIRED] -T Target File Name [REQUIRED] -F Output log of messages [standard output] -O Options d Delete new file first psRenameFile looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:21 psRenameFile ER00012 You requested the program's help function. --------------------------------------------------------- - - psReplaceStrings - - --------------------------------------------------------- psReplaceStrings: This program replaces all instances of 'old string' with 'new string' in . This program also backs up the original file to . You can specify an old and new string pair on the command line, or specify a file which contains old and new string pairs. Syntax: psReplaceStrings -Ifile -Nnew_file -Sold_string -snew_string -Pstring_pairs_file -Ooptions -Flog_file Arguments: -I Name of input file -N Name of new file [-I] -P Name of old-new strings pairs file -S Old string -s New string -F Output log of messages [standard output] -O Options i The old string must be surrounded by white spaces in the original text. d If the old string exists on the line, delete the entire line. w Keep leading and trailing whites spaces on old and new strings. Notes: The format of the strings pairs file is very simple. It has three columns. Each column is separated by one or more white spaces. The first column contains the old string. The second column contains the new string. The third column contains either 'D' which means to delete the line if old string is found, 'Y' which means the old string must be surrounded by white spaces for a match, or 'N' which means the old string does not have to be isolated by white spaces. Look in the source directory for this program to see an example strings pairs file. You do not have to use a strings pairs file. You can specify a single pair of strings on the command line using the -S and -s arguments. The advantage of using a strings pairs file is that you can specify up to 2000 pairs in a single file, with which psReplaceStrings in one pass, can change all the old string to new strings in your input file. psReplaceStrings looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:21 psReplaceStrings ER00012 You requested the program's help function. --------------------------------------------------------- - - psRotateLog - - --------------------------------------------------------- psRotateLog: This program rotates a log file. The programs receives log data from standard input. Syntax: psRotateLog -Mmax_bytes -Nmax_files -Ddirectory -Ffile_prefix -Ooptions Arguments: -M The maximum size of the log [1000000] bytes -N The maximum number of log files [10] -D Directory to write the logs [.] -F The prefix for the log file set [standard output] -O Options t Add a timestamp for every line psRotateLog looks for the environment variables: PS_SYSMSG_FILE Name of the tool's messages file. 2009.06.18 21:12:21 psRotateLog ER00012 You requested the program's help function. --------------------------------------------------------- - - psShowConfigValue - - --------------------------------------------------------- psShowConfigValue: Given the section and key name, this program prints the value associated with them from a DBPowerSuite configuration file. Syntax: psShowConfigValue -Nfile -Ssection -Kkey -Flog_file -Ooptions Arguments: -N Configuration File Name [/disk2/ceccareb/PS/dbaccess/parameters/ddl.conf] -S Section Name -K Key Name -F Output log of messages [standard output] -O Options v Just print the value. Do NOT show errors. psShowConfigValue looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:21 psShowConfigValue ER00012 You requested the program's help function. --------------------------------------------------------- - - psShowDirectorySizes - - --------------------------------------------------------- psShowDirectorySizes: This program shows the total sizes in bytes of all the files under a directory, and the total sizes of the files under each subdirectory. The size value of for the directory itself does not include the size of the files in its subdirectories. Syntax: psShowDirectorySizes -Ndirectory -Xexcluded-directories -Flog_file Arguments: -N Name of Directory [current directory] -X Excluded Directories -P Name Pattern [*] -F Output log of messages [standard output] psShowDirectorySizes looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:22 psShowDirectorySizes ER00012 You requested the program's help function. --------------------------------------------------------- - - psShowDiskPerformance - - --------------------------------------------------------- psShowDiskPerformance This program exercises the disk by performing many read and write operations. The reports the performance statistics when it completes. Syntax: psShowDiskPerformance -Idirectory_list_file -Tnumber_of_threads -Rrecord_size -Nnumber_of_records -Dnumber_of_files_per_dir -Flog_file Arguments: -I Input Text File Containing List of Directories [] -T Number of Threads to Launch [1] -R Record Size (bytes) [128] -N Number of Records per File [100000] -D Number of Files Per Dir [1] -F Output log of messages [standard output] -O Options s Flush records to disk after each write. psShowDiskPerformance looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:22 psShowDiskPerformance ER00012 You requested the program's help function. --------------------------------------------------------- - - psShowErrno - - --------------------------------------------------------- psShowErrno: This program displays the message associated with your operating system 'errno' code. Many poorly-written applications will show you 'error = 10204' without telling you what 10204 means. Many such error codes are the infamous 'errno' variable. If it is, you can use this program to translate the code into something meaningful. Syntax: psShowErrno -Eerrno -Foutput_file Arguments: -E Errno [REQUIRED] -F Name of file to write results [standard output] psShowErrno looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:22 psShowErrno ER00012 You requested the program's help function. --------------------------------------------------------- - - psShowLocale - - --------------------------------------------------------- psShowLocale: This program shows you your locale in your default environment. Generally speaking, you can change your locale by changing various operating system environment variables. On Solaris, these environment variables are LC_ALL, LC_CTYPE, LC_COLLATE, LC_TIME, LC_NUMERIC, LC_MONETARY and LC_MESSAGES. Syntax: psShowLocale -Foutput_file Arguments: -F Name of file to write results [standard output] psShowLocale looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:22 psShowLocale ER00012 You requested the program's help function. --------------------------------------------------------- - - psShowMessage - - --------------------------------------------------------- psShowMessage: By default, this program shows the text associated with a function or exit code of a DBPowerSuite function or program. However, if you have created own messages file (using the MessagesFile class), you can use this program to cross reference your own codes and messages. Syntax: psShowMessage -Ccode -Mmessages_file -Foutput_file Arguments: -C Exit or Return Code -M Name of Messages Index File [PS System Messages File] -F Name of File to Write Message Text. [standard output] psShowMessage looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:22 psShowMessage ER00012 You requested the program's help function. --------------------------------------------------------- - - psShowParameter - - --------------------------------------------------------- psShowParameter: This program shows the value of a parameter given the the name of the parameter. Syntax: psShowParameter -Pparameters_file -Foutput_text_file Arguments: -K Parameter Name -P Name of Parameters Index File -F Name of File to Write Parameter's Value [standard output] psShowParameter looks for the environment variables: PS_SYSPARAM_FILE When -P is not specified. PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:23 psShowParameter ER00012 You requested the program's help function. --------------------------------------------------------- - - psShowPerformance - - --------------------------------------------------------- psShowPerformance This program exercise the CPU and memory and reports the speed of the CPU and memory. Syntax: psShowPerformance -Tnumber_of_threads -Anumber_of_adds -Xnumber_of_multiplies -Dnumber_of_divides -Mnumber_of_memory_ops -Flog_file Arguments: -T Number of Threads to Launch [5] -A Number of add ops (in millions) [10] -X Number of mul ops (in millions) [10] -D Number of div ops (in millions) [10] -M Number of mem ops (in millions) [10] -F Output log of messages [standard output] psShowPerformance looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:23 psShowPerformance ER00012 You requested the program's help function. --------------------------------------------------------- - - psShowTime - - --------------------------------------------------------- psShowTime: This program converts time values from operating system integers to Gregorian format, and vice-versa. Syntax: psShowTime -Ttext -Foutput_file Arguments: -T Time (operating system time, see Notes.) [current time] -G Gregorian Time (YYYYMMDDHHMMSS) [current time] -F Name of file to write results [standard output] psShowTime looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. Notes: Most operating systems keep track of time with integers. The operating system time is expressed in 'seconds past midnight, Jan. 1, 1970 UT.' UT = Universal Coordinated Time. UT is important. The time(0) function always returns the number of seconds past midnight, Jan. 1, 1970 UT. UT is not the same as GMT. UT also includes leap seconds as computed by the US Naval Observatory. GMT does not. The US Naval Observatory adds about 1 leap second every 4 months as dictated by the position of the moon. This program will convert such UT integers into Gregorian format in the local time zone. If you enter the number in Gregorian format, then the program figures out the operating system time. This program also computes the Julian day. Julian day '0' is at 12 hours UT January 1, 4713 B.C. 2009.06.18 21:12:23 psShowTime ER00012 You requested the program's help function. --------------------------------------------------------- - - psSleep - - --------------------------------------------------------- psSleep: This program does nothing but go to sleep for N seconds. You can use this program as a platform-independent way of causing your shell script to pause. Syntax: psSleep -Nseconds -Foutput_file -Ooptions Arguments: -N Seconds To Sleep [30] -F Name of file to write results [standard output] -O Options w Do NOT write messages psSleep looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:23 psSleep:p12248 ER00012 You requested the program's help function. --------------------------------------------------------- - - psTestGetIPAddresses - - --------------------------------------------------------- psTestGetIPAddresses This program tests the ability of the operating system to resolve network addresses for computer names. Syntax: psTestGetIPAddresses -Tnumber_of_threads -Xiterations -Ccomputer_names -Sstack_size -Nnumber_of_files_to_open -Flog_file -Ooptions Arguments: -T Number of Threads to Launch [100] -X Iterations over list of computer names [100] -C Computer names (if a list, enclose in quotes) [firstsun] -S Stack size [1048576] -N Number of temp files to create & fopen() [0] -F Output log of messages [standard output] -O Options: n Do NOT do lookups o Do opens() instead of fopens() s Spawn a child process that does the same thing i Let child inherit parent's file descriptors psTestGetIPAddresses looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. Notes: This program opens N files. While they are open, this program makes calls to the operating system's version of gethostbyname. The problem is, is that most C I/O libraries only support up to 256 file descriptors. One can only have 256 standard C I/O descriptors open. If one tries to fopen() a C I/O file after that, fopen() fails. Also, gethostbyname_r() and gethostbyname() uses the standard C I/O library to fopen() some file under the covers. If the application is already using 256 descriptors and then calls such a gethostbyname, gethostbyname fails. While fopen() uses a standard C I/O file descriptor. open() does not. You can open() more files than fopen(). A descriptor is still a descriptor. It is just that the C I/O library sees the first 256. If the app opens() 256 times, there are no more file descriptors left for calls to fopen() to succeed. The app can still call open() hundreds of more times, but not fopen(). Remember that standard input, output and error are always open open. They consume 3 file descriptors. So really, you have only 253 available standard C I/O file descriptors. Watch out for thread size. On some platforms, a new thread allocates its entire stack up front. You may have to decrease the stack size in order to create additional threads. Spawning a child process. When -Os, this program spawns a child process after opening all its files. This tests how many files your child process can open. Since your child process inherits open file descriptors from its parent, the number of new files the child can create depends on how many the parent has opened. We are also testing the FD_CLOEXEC flag. 2009.06.18 21:12:23 psTestGetIPAddresses:p12250:t1 ER00012 You requested the program's help function. --------------------------------------------------------- - - psTestLibrary - - --------------------------------------------------------- psTestLibrary: This program tests whether a shared object library (.so file) can resolve its symbols by itself. If the library can, any program can successfully link with this library without having to link with any other library. If the library cannot resolve its symbols by itself, then psTestLibrary will not succeed in loading the library. You will get the name of the first unresolveable symbol. You should be able to look at the symbol and know what other library this symbol belongs to. If your test library is in C++, then your unresolved symbol will be 'mangled.' (The C++ compiler encodes the real function or symbol name.) Syntax: psTestLibrary library_name --------------------------------------------------------- - - psTranslateIP - - --------------------------------------------------------- psTranslateIP: This program translate an IP address in form N.N.N.N to an integer. Syntax: psTranslateIP -Iip_address -Foutput_file Arguments: -I IP or Network Address [208.209.85.3] -F Name of file to write results [standard output] psTranslateIP looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. Notes: An IP address is in the form of N.N.N.N If you enter such a value then this program translates the IP address to an integer network address. If you enter an integer, then this program assumes that your integer is already a network address and translates the network address to an IP address of form N.N.N.N. 2009.06.18 21:12:24 psTranslateIP ER00012 You requested the program's help function. --------------------------------------------------------- - - psUPing - - --------------------------------------------------------- psUPing: This program tells you if TCP/IP servers are listening and accepting connections. You specify a target computer and a range of TCP/IP ports. If psUPing connects to the servers listening on those ports, then the program returns 0--all servers are up and listening. If the program cannot connect to any one of the servers, it returns non-zero. The program only writes verbose messages to the output log if you tell it to (-Os). The program does nothing other than to connect to each server. Once the program connects, it immediately disconnects. It does not send data to any server. It does receive data from any server. Syntax: psUPing -Iserver_ip_or_host_name -Pstarting_port_number -Eending_port_number -Ttimeout -Foutput_log -Ooptions Arguments: -S Server's IP address or hostname [firstsun] -P Starting port number [80] -E Ending port number [Starting port number] -T Timeout (seconds) [3] -F Name of output log [standard output] -O Options s Write exit status to output log e Turn on messages g See only good connections psUPing looks for the environment variables: PS_SYSMSG_FILE For the location of the system-wide messages file. 2009.06.18 21:12:24 psUPing ER00012 You requested the program's help function. --------------------------------------------------------- - - psWhatVersion - - --------------------------------------------------------- psWhatVersion: This program tells you what the version of DBPowerSuite you are running. Syntax: psWhatVersion -s Arguments: -s Tells you the short version. The short version does not include the platform ID.