Publishing SAS Formats in DB2

Overview of the Publishing Process

The SAS publishing macros are used to publish formats and the SAS_PUT() function in DB2.
Note: SFTP is used to transfer the source files to the DB2 server during the publishing process. Certain software products that support SSH-2 or SFTP protocols must be installed before you can use the publishing macros. For more information, see the SAS In-Database Products: Administrator's Guide.
The %INDB2_PUBLISH_FORMATS macro creates the files that are needed to build the SAS_PUT() function and publishes those files to the DB2 database.
This macro also makes many formats that SAS supplies available inside DB2. In addition to formats that SAS supplies, you can also publish the PROC FORMAT definitions that are contained in a single SAS format catalog by using the FMTCAT= option. The process of publishing a PROC FORMAT catalog entry converts the range label pairs into embedded data in DB2.
The %INDB2_PUBLISH_FORMATS macro performs the following tasks:
  • produces the set of .c and .h files that are necessary to build the SAS_PUT() function
  • produces a script of the DB2 commands that are necessary to register the SAS_PUT() function in the DB2 database
  • transfers the .c and .h files to DB2 using SFTP
  • calls the SAS_COMPILEUDF function to compile the source files into object files and to link to the SAS Formats Library for DB2
  • calls the SAS_DELETEUDF function to remove existing object files and then replaces them with the new object files
  • uses the SAS/ACCESS Interface to DB2 to run the script and publish the SAS_PUT() function to the DB2 database
The SAS_PUT() function is registered in DB2 with shared object files that are loaded at run time. These functions must be stored in a permanent location. The SAS object files and the SAS Formats Library for DB2 are stored in the db2path/SQLLIB/FUNCTION/SAS directory where you supply the db2path. This directory is accessible to all database partitions.
DB2 caches the object files after they are loaded. Each time the updated objects are used, you must either stop and restart the database to clean up the cache, or rename the object files and register the functions with the new object filenames. The SAS publishing process automatically handles the renaming to avoid stopping and restarting the database.

Running the %INDB2_PUBLISH_FORMATS Macro

To run the %INDB2_PUBLISH_FORMATS macro, follow these steps:
  1. Start SAS 9.3, and submit these commands in the Program Editor or the Enhanced Editor: 
    %indb2pf;
%let indconn = server=yourserver user=youruserid password=yourpwd
   database=yourdb schema=yourschema serveruserid=yourserveruserid;
    For more information, see %INDB2PF Macro and INDCONN Macro Variable.
  2. Run the %INDB2_PUBLISH_FORMATS macro.
    For more information, see %INDB2_PUBLISH_FORMATS Macro Syntax.
    Messages are written to the SAS log that indicate whether the SAS_PUT() function was successfully created.

%INDB2PF Macro

The %INDB2PF macro is an autocall library that initializes the format publishing software.

INDCONN Macro Variable

The INDCONN macro variable is used as credentials to connect to DB2. You must specify the server, user, password, and database. The schema name and server user ID are optional. You must assign the INDCONN macro variable before the %INDB2_PUBLISH_FORMATS macro is invoked.
Here is the syntax for the value of the INDCONN macro variable:
SERVER=server USER=userid PASSWORD=password
DATABASE=database <SCHEMA=schemaname> <SERVERUSERID=serveruserid>
Arguments
SERVER=server
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 the way the host name was cached when PSFTP 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 PSFTP 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 PSFTP command, see DB2 Installation and Configuration Steps in the SAS In-Database Products: Administrator's Guide.
USER=userid
specifies the DB2 user name (also called the user ID) that is used to connect to the database.
PASSWORD=password
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.
DATABASE=database
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.
SCHEMA=schema
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.
SERVERUSERID=serveruserid
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 you with the SERVERUSERID (SFTP user ID) and the private key that need to be added to the pageant.exe (Windows) or SSH agent (UNIX). Pageant must be running for the SFTP process to be successful.
Tip
The INDCONN macro variable is not passed as an argument to the %INDB2_PUBLISH_FORMATS macro. This information can be concealed in your SAS job. You might want to place it in an autoexec file and set the permissions on the file so that others cannot access the user ID and password.

%INDB2_PUBLISH_FORMATS Macro Syntax

%INDB2_PUBLISH_FORMATS (
<DATABASE=database–name>
<, FMTCAT=format-catalog-filename>
<, FMTTABLE=format-table-name>
<, ACTION=CREATE | REPLACE | DROP>
<, MODE=PROTECTED | UNPROTECTED>
<, INITIAL_WAIT=wait-time>
<, FTPTIMEOUT=timeout-time>
<, OUTDIR=diagnostic-output-directory>
);
Arguments
DATABASE=database-name
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.
FMTCAT=format-catalog-filename
specifies the name of the format catalog file that contains all user-defined formats that were created with the FORMAT procedure and will be 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.
FMTTABLE=format-table-name
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 table contains the columns shown in Table 2.1.
Format Table Columns
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.
ACTION=CREATE | REPLACE | DROP
specifies that the macro performs one of these actions:
CREATE
creates a new SAS_PUT() function.
REPLACE
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.
DROP
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.
MODE=FENCED | UNFENCED
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.
INITIAL_WAIT=wait-time
specifies the initial wait time in seconds for SAS SFTP to parse the responses and complete the SFTP batchfile 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 batchfile 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
FTPTIMEOUT=time-out-value
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 batchfile 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
OUTDIR=diagnostic-output-directory
specifies a directory that contains diagnostic files.
Files that are produced include an event log that contains detailed information about the success or failure of the publishing process.

Modes of Operation

There are two modes of operation when executing the %INDB2_PUBLISH_FORMATS macro: fenced and unfenced. You specify the mode by setting the MODE= argument.
The default mode of operation is fenced. Fenced mode means that the macro code is isolated in a separate process in the DB2 database, and an error does not cause the database to stop. It is recommended that you run the %INDB2_PUBLISH_FORMATS macro in fenced mode during acceptance tests.
When the %INDB2_PUBLISH_FORMATS macro is ready for production, you can rerun the macro in unfenced mode. Note that you should see a significant performance advantage when you republish the formats in unfenced mode

Format Publishing Macro Example

%indb2pf;
%let indconn = server=db2base user=user1 password=open1
database=mydb
   schema=myschema;
%indb2_publish_formats(fmtcat= fmtlib.fmtcat);
This sequence of macros generates .c and .h files for each data type. The format data types that are supported are numeric (FLOAT, INT), character, date, time, and timestamp (DATETIME). The %INDB2_PUBLISH_FORMATS macro also produces a text file of DB2 CREATE FUNCTION commands that are similar to these: 
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'
After it is installed, you can call the SAS_PUT() function in DB2 by using SQL. For more information, see Using the SAS_PUT() Function in the DB2 Database.
Copyright © SAS Institute Inc. All rights reserved.