db2path/SQLLIB/FUNCTION/SAS
directory
where you supply the db2path
.
This directory is accessible to all database partitions.
%let indconn = server=yourserver user=youruserid password=yourpwd database=yourdb schema=yourschema serveruserid=yourserveruserid;
specifies the DB2 server name or the IP address of the server host. If the server name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.
Requirement | The name must be consistent with how the host name was cached when SFTP server was run from the command window. If the full server name was cached, you must use the full server name in the SERVER argument. If the short server name was cached, you must use the short server name. For example, if the long name, disk3295.unx.comp.com, is used when SFTP was run, then server=disk3295.unx.comp.com must be specified. If the short name, disk3295, was used, then server=disk3295 must be specified. For more information about running the SFTP command, see DB2 Installation and Configuration Steps in SAS In-Database Products: Administrator’s Guide. |
specifies the DB2 user name (also called the user ID) that is used to connect to the database.
specifies the password that is associated with your DB2 user ID.
Tip | Use only PASSWORD=, PASS=, or PW= for the password argument. PWD= is not supported and causes an error. |
specifies the DB2 database that contains the tables and views that you want to access.
Requirement | The format functions are created as Unicode functions.
If the database is not a Unicode database, then the alternate collating
sequence must be configured to use identity_16bit .
|
specifies the schema name for the database.
Default | If you do not specify a value for the SCHEMA argument, the value of the USER argument is used as the schema name. |
specifies the user ID for SAS SFTP and enables you to access the machine on which you have installed the DB2 database.
Default | If you do not specify a value for the SERVERUSERID argument, the value of the USER argument is used as the user ID for SAS SFTP. |
Note | The person who installed and configured the SSH software can provide the SERVERUSERID (SFTP user ID) and the private key that need to be added to the pageant.exe (Windows) or SSH agent (UNIX). In order for the SFTP process to be successful, Pageant must be running on Windows, and the SSH agent must be running on UNIX. |
specifies the name of a DB2 database to which the SAS_PUT( ) function and the formats are published. This argument lets you publish the SAS_PUT( ) function and the formats to a shared database where other users can access them.
Requirement | The format functions are created as Unicode functions.
If the database is not a Unicode database, then the alternate collating
sequence must be configured to use identity_16bit .
|
Interaction | The database that is specified by the DATABASE= argument takes precedence over the database that you specify in the INDCONN macro variable. For more information, see Running the %INDB2_PUBLISH_FORMATS Macro. |
Tip | It is not necessary that the format definitions and the SAS_PUT( ) function reside in the same database as the one that contains the data that you want to format. You can use the SQLMAPPUTO= system option to specify the database where the format definitions and the SAS_PUT( ) function have been published. |
specifies the name of the format catalog file that contains all user-defined formats that were created with the FORMAT procedure and are made available in DB2.
Default | If you do not specify a value for FMTCAT= and you have created user-defined formats in your SAS session, the default is WORK.FORMATS. If you do not specify a value for FMTCAT= and you have not created any user-defined formats in your SAS session, only the formats that SAS supplies are available in DB2. |
Interaction | If the format definitions that you want to publish exist in multiple catalogs, you must copy them into a single catalog for publishing. If you specify more than one format catalog using the FMTCAT argument, only the last catalog that you specify is published. |
See | Considerations and Limitations with User-Defined Formats |
specifies the name of the DB2 table that contains all formats that the %INDB2_PUBLISH_FORMATS macro creates and that the SAS_PUT( ) function supports. The format table contains the columns shown in the following table.
Column Name
|
Description
|
---|---|
FMTNAME
|
specifies the name of
the format.
|
SOURCE
|
specifies the origin
of the format. SOURCE can contain one of these values:
SAS
supplied by SAS
PROCFMT
User–defined
with PROC FORMAT
|
Default | If FMTTABLE is not specified, no table is created. You can see only the SAS_PUT( ) function. You cannot see the formats that are published by the macro. |
Interaction | If ACTION=CREATE or ACTION=DROP is specified, messages are written to the SAS log that indicate the success or failure of the table creation or drop. |
specifies that the macro performs one of these actions:
creates a new SAS_PUT( ) function.
overwrites the current SAS_PUT( ) function, if a SAS_PUT( ) function is already registered or creates a new SAS_PUT( ) function if one is not registered.
causes the SAS_PUT( ) function to be dropped from the DB2 database.
Interaction | If FMTTABLE= is specified, both the SAS_PUT( ) function and the format table are dropped. If the table name cannot be found or is incorrect, only the SAS_PUT( ) function is dropped. |
Default | CREATE |
Tip | If the SAS_PUT( ) function was defined previously and you specify ACTION=CREATE, you receive warning messages from DB2. If the SAS_PUT( ) function was defined previously and you specify ACTION=REPLACE, a message is written to the SAS log indicating that the SAS_PUT( ) function has been replaced. |
specifies whether the running code is isolated in a separate process in the DB2 database so that a program fault does not cause the database to stop.
Default | FENCED |
Tip | Once the SAS formats are validated in fenced mode, you can republish them in unfenced mode for a significant performance gain. |
specifies the initial wait time in seconds for SAS SFTP to parse the responses and complete the SFTP batch-file process.
Default | 15 seconds |
Interactions | The INITIAL_WAIT= argument works in conjunction with the FTPTIMEOUT= argument. Initially, SAS SFTP waits the amount of time specified by the INITIAL_WAIT= argument. If the SFTP batch-file process is not complete after the initial wait time, retries occur until the wait time is equal to or greater than the time-out value specified by the FTPTIMEOUT= argument. All retries double the previous wait time. SAS SFTP fails after the time-out value is reached or exceeded. An error message is written to the SAS log. |
For example, assume that you use the default values. The initial wait time is 15 seconds. The first retry waits 30 seconds. The second retry waits 60 seconds. The third retry waits 120 seconds, which is the default time-out value. So the default initial wait time and time-out values enable four possible tries: the initial try, and three retries. | |
See | FTPTIMEOUT= argument |
specifies the time-out value in seconds if SAS SFTP fails to transfer the files.
Default | 120 seconds |
Interactions | The FTPTIMEOUT= argument works in conjunction with the INITIAL_WAIT= argument. Initially, SAS SFTP waits the amount of time specified by the INITIAL_WAIT= argument. If the SFTP batch-file process is not complete after the initial wait time, retries occur until the wait time is equal to or greater than the time-out value specified by the FTPTIMEOUT= argument. All retries double the previous wait time. SAS SFTP fails after the time-out value is reached or exceeded and an error message is written to the SAS log. |
For example, assume you use the default values. The initial wait time is 15 seconds. The first retry waits 30 seconds. The second retry waits 60 seconds. The third retry waits 120 seconds, which is the default time-out value. So the default initial wait time and time-out values enable four possible tries: the initial try, and three retries. | |
Tip | Use this argument to control how long SAS SFTP waits to complete a file transfer before timing out. A time-out failure could indicate a network or key authentication problem. |
See | INITIAL_WAIT argument |
specifies a directory that contains diagnostic files.
See | Special Characters in Directory Names |
%let indconn = server=db2base user=user1 password=open1 database=mydb schema=myschema; %indb2_publish_formats(fmtcat= fmtlib.fmtcat);
CREATE FUNCTION sas_put(float , varchar(256)) RETURNS VARCHAR(256) LANGUAGE C PARAMETER STYLE npsgeneric CALLED ON NULL INPUT EXTERNAL CLASS NAME 'Csas_putn' EXTERNAL HOST OBJECT '/tmp/tempdir_20090528T135753_616784/formal5.o_x86' EXTERNAL NSPU OBJECT '/tmp/tempdir_20090528T135753_616784/formal5.o_diab_ppc'