sybsystemprocs.dbo.sp_csv



sp_csv is a stored procedure that can be used to copy tables, views, procedures, and triggers between databases and between servers. It can also copy tables and views between Sybase and Oracle. There is also a version of sp_csv on Oracle that allows you to copy tables and views to Sybase. To execute you must have sa_role.

The best way to invoke it is through the Sybercron Register because many of the parameters are presented as a drop-down list of valid values. But be careful. If you leave out something important, like the name of the object you want to copy, you end up copying all objects.

Procedure Parameters:
BUTTON:
SRCESERVER:
SRCEDATABASE:
SRCEOWNER:
SRCENAME:
SRCETYPE:
DESTSERVER:
DESTDATABASE:
DESTOWNER:
DESTNAME:
ROWCOUNT:
DDL:
CSV:
UPLOAD:
MAILTO:
TABLESPACE:
INDEXSPACE:



invoking sp_csv


Parameter Comments 
BUTTON
 The only values of interest are MULTIFILE and SINGLEFILE. MULTIFILE means each object found is written to a separate file determined by sp_csv. SINGLEFILE writes to a single file determined by you with the value of SCRIPTNAME. SINGLEFILE dumps only the DDL of stored procedures. 
SRCESERVER
 This is the server name where the source objects are to be copied from. Even though you are logged into HRPAY (for example), you may specify any valid server name. The list of server names are stored in the table http.dbo.CRON_KEYWORDS. 
SRCEDATABASE
 This is the name of the database where the source objects are to be copied from. It is a text field which means you must type the name yourself. Be careful if you are on a case-sensitive server that the name is cased correctly.

In Oracle there is only one database, but to keep the naming consistent between Sybase and Oracle, use SRCEDATABASE for the owner and use dbo for SRCEOWNER. 

SRCEOWNER
 For Oracle you should specify "dbo." For Sybase you can either specify the owner (like DELTEK or dbo) or leave this blank to specify all owners. 
SRCENAME
 This is the object name. Leave this blank to specify all objects owned by SRCEOWNER. 
SRCETYPE
 This is the object type. Leave this blank to specify all object types.

SRCETYPE, SCRENAME and SRCEOWNER work hand-in-hand with each other; for example, to dump all procedures in database DELTEK to a single file, the SRCEOWNER would be NULL, the SRCENAME would be NULL, the SRCETYPE would be PROCEDURE, and BUTTON would be set to SINGLEFILE. This is how sp_defncopy calls sp_csv. 

DESTSERVER
 This is the name of the server where these objects are to be copied to. DB8 is the server name for Oracle. 
DESTDATABASE
 If you are copying objects to Oracle, then you really want to specify the owner or schema name here. 
DESTOWNER
 If you are copying objects to Oracle, then you want to specify dbo here. 
DESTNAME
 This field determines the name of the destination object. You can use it to change the name of procedures, triggers, views and tables. All occurences of SRCENAME are replaced by DESTNAME, but the replacement is case-sensitive. If your use and your server are case-insensitive, then you could run then risk that not all occurences are replaced. Normally, SRCENAME and DESTNAME are the same or DESTNAME is NULL in which case its name is determined from SRCENAME. Specifying a DESTNAME while SRCENAME is NULL doesn't make sense. 
ROWCOUNT
 ROWCOUNT determines the maximum number of rows that should be exported. The default is NULL which means all rows will be exported. 
DDL
 This option determines if the object's definition should be created. This should be TRUE for procedures, triggers and views. It should also be TRUE for tables if the table does not exist or you want to recreate the table. Specifying FALSE for tables makes sense if all you want to do is refresh the data. 
CSV
 For tables this option determines whether or not the data in that table should be copied as well. If FALSE, the table is created without any data. If TRUE and DDL is FALSE, it is assumed that the table already exists. In this case, the table is deleted before the data is copied. 
UPLOAD
 UPLOAD determines whether or not the files should actually be loaded with Sybercron. If FALSE, the files are created and transferred, but not loaded. If TRUE, the files are loaded and removed.

If you choose FALSE, you can run the upload later. The procedure that does this is called HTTP.dbo.UPLOAD. It requires a filename and e-mail address. A filename takes the form 

SERVER.DATABASE.OWNER.NAME
but SERVER, DATABASE, and OWNER will be assumed if you omit them. 
MAILTO
 All results are sent to this e-mail address. It defaults to the e-mail address that you used to register sp_csv. 
TABLESPACE
 This option specifies the tablespace for tables. If you are copying a table to Sybase, this option is ignored. If not specified, your table is created in your schema's default tablespace. 
INDEXSPACE
 This option specifies the tablespace for indexes. If not specified, TABLESPACE name is used instead.




Tables that drive SP_CSV



The table http.dbo.LEIBNITZ_HOSTS exists on all hosts and contains information that is required by SP_CSV, the most imporant of which determines if the destination platform is Oracle or Sybase. The DDL and the information within should be identical among all servers. sp_html_leibnitz also use this table to include all Oracle server names (since they cannot be determined dynamically).


Name Comments 
DESTSERVER
 This is the server name defined in Sybase's interface file or Oracle's nsnames.ora file. Both represent valid server names (or hostnames) accessible through Sybernet. 
HOSTNAME
 This is the unix hostname to use when transferring files between Sybase and Oracle. Sybase files originate from spock.sri.com while Oracle files originate from tabit.sri.com. 
USERNAME
 This is the username to use when transferring files between hosts. It is the unix username that owns the destination directory for all file transfers (when required). The username for spock.sri.com is www and the username for tabit.sri.com is oracle. 
DIR
 This is the full pathname that will be used to transfer your files with SCP2. Files are transferred only when HOSTNAME is different from the current host. If the two hosts are the same, then it is assumed the files are written and read from the same directory (and hence no transfer takes place). If the host names are not the same, then this is the directory that should be used to transfer the files and where Sybernet expects to see them reside. 
PLATFORM
 This field determines if the destination server is SYBASE or ORACLE. It is required because the routines that actually map objects between different platforms needs to know the destination. While the source platform can be assumed, it is vital to know if destination platform.



Here is what the table looks like in DB8 (Oracle).

DESTSERVERHOSTNAMEUSERNAMEDIRPLATFORM
DEVPAYspock.sri.comwww/webservers/etc/httpd/cgi-bin/SCRIPTSSYBASE
HRPAYspock.sri.comwww/webservers/etc/httpd/cgi-bin/SCRIPTSSYBASE
SRIMISspock.sri.comwww/webservers/etc/httpd/cgi-bin/SCRIPTSSYBASE
NEWDSSspock.sri.comwww/webservers/etc/httpd/cgi-bin/SCRIPTSSYBASE
DEVDSSspock.sri.comwww/webservers/etc/httpd/cgi-bin/SCRIPTSSYBASE
DB8tabit.sri.comoracle/home/oracle/Sybernet/SCRIPTSORACLE
TESLAtabit.sri.comoracle/home/oracle/Sybernet/SCRIPTSORACLE
ORADEVtabit.sri.comoracle/home/oracle/Sybernet/SCRIPTSORACLE



The table http.dbo.CRON_KEYWORDS is used by SP_HTML_CRON_REGISTER to create a list of valid options when invoking SP_CSV. For examples, just list this table from any server that SP_CSV runs from.


Name Comments 
NAME
 SRCESERVER is the list of valid servernames that SP_CSV may be invoked from. For Sybase, you may list all valid remote servers and all Oracle Servers. For Oracle, only the current servername should be listed.

DESTSERVER is the list of valid servernames that SP_CSV may be invoked to. For Sybase and Oracle you may include all valid remote servers (including the SRCESERVER).

SRCETYPE lists all the possible types that may be transferred. You will want to include all valid types for the source server, although it doesn't make sense to transfer a FUNCTION from Oracle to Sybase.

DDL is a Boolean (a string) that determines if the DDL (Data Definition Language) is required.

CSV is a Boolean (a string) that determines if the data (Comma Separated Values) is required.

UPLOAD is a Boolean (a string) that determines if the DDL and CSV scripts should be uploaded to the destination server. When FALSE, the script (SERVERNAME.DATABASE.OWNER.NAME.sql) is created, but not uploaded. 

VALUE
 The list of valid options. For Boolean's, TRUE and FALSE should be specified. For all other values see above. 
TYPE
 This should be SELECT. 
SIZE
 This should be set to "1". 
STYLE
 Determines the datetime style when TYPE is DATETIME. This should be NULL. 
minValue
 Determines the minimum value allowed. This should be NULL. 
maxValue
 Determines the maximum value allowed. This should be NULL. 
ALIGN
 Determines the alignment of numeric values. This should be NULL. 
SCALE
 Determines the scaling of numeric data. This should be NULL. 
COMMA
 Determines the separator in a list of SELECT values. This should be NULL. 
CHECKED
 Determines the default value for this field.



Here is what the table looks like in DB8 (Oracle).

NAMEVALUETYPESIZE
DESTSERVERDEVPAY,HRPAY,SRIMIS,NEWDSS,DEVDSS,DB8,TESLA,ORADEVSELECT1
DEBUGFALSE,TRUESELECT1
MAILTO%40EMAILTEXT30
DDLFALSE,TRUESELECT1
CSVFALSE,TRUESELECT1
SRCESERVERDB8SELECT1
BUTTON CHOOSE1
UPLOADFALSE,TRUESELECT1
SRCETYPE,FUNCTION,PROCEDURE,TABLE,TRIGGER,VIEWSELECT1









Sybernet Library Maintenance (sp_html_leibnitz)



Although sa_role is required to run sp_csv, Sybernet Library Maintenance can be invoked by anyone. If you have sa_role you can copy any object you want, even between servers if you have a remote login in that server. If you do not have sa_role, then you may copy any objects that you own. If you are copying to another Sybase server (even the same server) then you will also need the approprate permission to create objects. For example, in database DELTEK you might have the permission to create views, but if don't have that same permission in database HTTP, then you will not be able to copy views from DELTEK to HTTP. You may, however, copy tables and views that you own to Oracle.

Having GRANT SELECT on an object is not good enough to use this facility. You must either own it because you are the DBO (database owner) or you must be aliased to DBO.

The results of the example below are exactly the same as the example above.



 
Source Server:
Source Database:
Source Owner:
Source Type:
 
Destination Server:
Destination Database:
 
Rowcount:
Tablespace:
Indexspace:
 
Copy Register:
 
 



invoking sp_html_leibnitz


Parameter Comments 
Source Server
 This is exactly the same as SRCESERVER in sp_csv. The difference is that you must be a remote user in each server you wish to access. When setting up remote users, however, be sure to specify them in both servers. 
Source Database
 This is exactly the same as SRCEDATABASE in sp_csv. The list of database names is built dynamcially anytime you change the source server. If a database is in single-user mode or is damaged or is recoverying, it will not appear in the drop-down list. 
Source Owner
 This is exactly the same as SRCEOWNER in sp_csv except that the list of owners is built dynamcially each time you change the source database. 
Source Type
 This is exactly the same as SRCETYPE in sp_csv. 
Destination Server
 This is exactly the same as DESTSERVER in sp_csv. It unconditionally includes DB8 as a valid server name. 
Destination Database
 This is exactly the same as DESTDATABASE in sp_csv. If the destination server is DB8 (Oracle), then the database shown here will be exactly the same as the source database. That means if you are in database DELTEK and copy a table to Oracle, the only database you can choose is DELTEK.

It's confusing, but there is only one database in Oracle. What is referred to here as the destination database is really the owner in Oracle. Of course, DELTEK must also be a valid user in DB8 for the copy to be successful. 

Rowcount
 This is exactly the same as ROWCOUNT in sp_csv. This drop-down list allows you to specify how many rows you wish to copy from a table. 
Tablespace
 This is exactly the same as TABLESPACE in sp_csv. If specified, this value determines the tablespace for you tables. If not specified, your default tablespace is used instead. 
Indexspace
 This is exactly the same as INDEXSPACE in sp_csv. If specified, this value determines the tablespace for your indexes. If not specified, Tablespace is used instead. 
Copy Register
 When checked, this option copies this procedure's register information. Currently, register information cannot be copied from Sybase to Oracle.








Internal Files


Filename Description 
filename.ddl
 This file contains the DDL (Data Definition Language) for this object. It contains everything needed to create this object. 
filename.idx
 This file contains the DDL for any indexes that span this table. 
filename.csv
 This file contains the comma separated data if the object you are copying is a table. 
filename.dbg
 This file contains a count of the number of rows if the object you are copying is a table. It is used throughout the copy to determine that all rows were extracted and inserted. 
filename.sql
 This is the final script that combines all of the information from the above 4 files. If you are copying a trigger, view, or procedure, only the DDL file is used to create this file. If you are copying a table, then the DDL, IDX and CSV files are combined to create this file.

After the SQL file is created, the DDL, IDX, CSV, and DBG files are discarded. After the SQL file is submitted through Sybercron, it too is discarded.




CSV E-mail


This is an example of the e-mail that is sent by the CSV process on Unix once it has created the SQL file that is then submitted through Sybercron. The input parameters describe what needs to be done and where the SQL file should be submitted. In this example, CSV is running on spock, but the SQL file must be transmitted to tabit.sri.com using SCP (Secure Copy Client) so that it can be read by Sybercron. Once transferred, CSV then contacts Sybernet and tells it to upload this file. 

     Subject: HRPAY.HRPRD.DBO.PS_PERSONAL_DATA.sql
        Date: Sat, 06 Oct 2001 08:01:56 -0700
        From: Sybernet@spock.sri.com
Organization: SRI International
          To: denis@unix.sri.com


STARTING SYNC USING THE FOLLOWING PARAMETERS:

 SRCENAME    = HRPAY.HRPRD.dbo.PS_PERSONAL_DATA
 DESTNAME    = DB8.HRPRD.dbo.PS_PERSONAL_DATA
 SRCETYPE    = TABLE
 DDL         = TRUE
 CSV         = TRUE
 UPLOAD      = TRUE
 IDENTITY    = NULL
 EMAIL       = denis@unix.sri.com
 HOSTNAME    = tabit.sri.com
 USERNAME    = oracle
 DIR         = /home/oracle/Sybernet/SCRIPTS

Total rows: 5267 (in table)
Total rows: 5267 (exported)
Total errors: 0 (column > 4000)

Starting transfer of DB8.HRPRD.DBO.PS_PERSONAL_DATA.sql from spock to tabit...
You have no controlling terminal. Can't initialize readline for confirmations.
Transfer successful!

Contacting Sybernet at tabit.sri.com...
Table DB8.HRPRD.DBO.PS_PERSONAL_DATA (Queued 7231)

Success!



Sybercron E-mail


This is an example of the e-mail that is sent from Sybercron after your SQL file has been submitted. Notice that the Subject has changed from the source filename to the destination filename. In the e-mail above, we were creating the SQL file from HRPAY. In this e-mail we are submitting the script to DB8. 


     Subject: DB8.HRPRD.DBO.PS_PERSONAL_DATA
        Date: Sat, 06 Oct 2001 08:03:49 -0700
        From: Sybernet@tabit.sri.com
Organization: SRI International
          To: denis@unix.sri.com

CREATING TABLE HRPRD.PS_PERSONAL_DATA...


0 rows affected.
0 rows affected.

LOADING TABLE HRPRD.PS_PERSONAL_DATA...


5267 rows affected.

Success!