DB2 Installation and Configuration

DB2 Installation and Configuration Steps

  1. If you are upgrading from a previous version, follow the instructions in Upgrading from a Previous Version.
  2. Verify that you can use PSFTP from Windows to UNIX without being prompted for a password or cache.
    To do this, enter the following commands from the PSFTP prompt, where userid is the user ID that you want to log on as and machinename is the machine to which you want to log on.
    psftp> open userid@machinename
    psftp> ls
  3. Install the SAS formats library, the binary files for the SAS_COMPILEUDF and SAS_DELETEUDF functions, and the SAS Embedded Process.
  4. Run the %INDB2_PUBLISH_COMPILEUDF macro to create the SAS_COMPILEUDF function.
  5. Run the %INDB2_PUBLISH_DELETEUDF macro to create the SAS_DELETEUDF function.
    For more information, see Running the %INDB2_PUBLISH_DELETEUDF Macro.
  6. If you plan to use SAS Model Manager with the SAS Scoring Accelerator for in-database scoring, perform the additional configuration tasks provided in Configuring SAS Model Manager.

Upgrading from a Previous Version

Overview of Upgrading from a Previous Version

You can upgrade from a previous version of the SAS Formats Library and binary files, the SAS Embedded Process, or both. See the following topics:

Upgrading the SAS Formats Library, Binary Files, and the SAS Embedded Process

To upgrade a previous version of the SAS Formats Library, binary files, and the SAS Embedded Process, follow these steps.
Note: These steps also apply if you want to upgrade only the SAS Formats Library and binary files. If you want to upgrade only the SAS Embedded Process, see Upgrading the SAS Embedded Process.
  1. Drop the SAS_COMPILEUDF and SAS_DELETEUDF functions by running the %INDB2_PUBLISH_COMPILEUDF and %INDB2_PUBLISH_DELETEUDF macros with ACTION=DROP.
    Here is an example.
    %let indconn = user=abcd password=xxxx database=indbdb server=indbsvr;
    %indb2_publish_compileudf(action=drop, db2path=/users/db2v10/sqllib, 
       compiler_path=/usr/vac/bin);
    %indb2_publish_deleteudf(action=drop);
  2. Confirm that the SAS_COMPILEUDF and SAS_DELETEUDF functions were dropped.
    Here is an example.
    proc sql noerrorstop;
       connect to db2 (user=abcd password=xxxx database=indbdb);
       select * from connection to db2 (
          select cast(funcname as char(40)), 
             cast(definer as char(20)) from syscat.functions 
                where funcschema='SASLIB' );
    quit;
    
    If you are upgrading only the SAS Formats Library and the binary files, skip to Step 6.
  3. Enter the following command to see whether the SAS Embedded Process is running.
    ps -ef | grep db2sasep
    If the SAS Embedded Process is running, results similar to this are displayed.
    db2v10 23265382 20840668   0   Oct 06      -  4:03 db2sasep 
    db2v10 27983990 16646196   1 08:24:09 pts/10  0:00 grep db2sasep
    
  4. Stop the DB2 SAS Embedded Process using DB2IDA command.
    Use this command to stop the SAS Embedded Process.
    db2ida -provider sas -stop
    If the SAS Embedded Process is still running, an error occurs. Enter this command to force the SAS Embedded Process to stop.
    db2ida -provider sas -stopforce
    For more information about the DB2IDA command, see Controlling the SAS Embedded Process for DB2.
  5. Remove the SAS directory that contains the SAS Embedded Process binary files from the DB2 instance path.
    • If you are upgrading a version of the SAS Embedded Process before the fourth maintenance release of SAS 9.4, enter the following commands to move to the db2instancepath directory and remove the SAS directory. db2instancepath is the path to the SAS Embedded Process binary files in the DB2 instance.
      cd db2instancepath
      rm -fr SAS
    • If you are upgrading a version of the SAS Embedded Process starting with the fourth maintenance release of SAS 9.4, enter the following commands to move to the db2instancepath directory and remove the SAS directory. db2instancepath is the path to the SAS Embedded Process binary files in the DB2 instance.
      cd db2instancepath
      rm -fr SASEPHome
  6. Stop the DB2 instance.
    1. Log on to the DB2 server and enter this command to determine whether there are any users connected to the instance:
      db2 list applications
    2. If any users are connected, enter these commands to force them off before the instance is stopped and clear any background processes:
      db2 force applications all
      db2 terminate
    3. Enter this command to stop the DB2 instance:
      db2stop
      
  7. Enter the following commands to move to the db2instancepath/sqllib/function directory and remove the SAS directory. db2instancepath/sqllib/function is the path to the SAS_COMPILEUDF and SAS_DELETEUDF functions in the DB2 instance.
    cd db2instancepath/sqllib/function
    rm -fr SAS

Upgrading the SAS Embedded Process

To upgrade the SAS Embedded Process, follow these steps.
Note: These steps are for upgrading the SAS Embedded Process. If you want to upgrade the SAS Formats Library and binary files or both the SAS Formats Library and binary files and the SAS Embedded Process, you must follow the steps in Upgrading the SAS Formats Library, Binary Files, and the SAS Embedded Process.
  1. Enter the following command to see whether the SAS Embedded Process is running.
    ps -ef | grep db2sasep
    If the SAS Embedded Process is running, results similar to this are displayed.
    db2v10 23265382 20840668   0   Oct 06      -  4:03 db2sasep 
    db2v10 27983990 16646196   1 08:24:09 pts/10  0:00 grep db2sasep
    
  2. Enter the following command to determine whether there are any users connected to the instance.
    db2 list applications
  3. Stop the DB2 SAS Embedded Process using DB2IDA command.
    Note: If you are upgrading the SAS Embedded Process, you do not need to shut down the database. The DB2IDA command enables you to upgrade only the SAS Embedded Process components without impacting clients already connected to the database. For more information about the DB2IDA command, see Controlling the SAS Embedded Process for DB2.
    Use this command to stop the SAS Embedded Process.
    db2ida -provider sas -stop
    If the SAS Embedded Process is still running, an error occurs. Enter this command to force the SAS Embedded Process to stop.
    db2ida -provider sas -stopforce
  4. Remove the SAS directory that contains the SAS Embedded Process binary files from the DB2 instance path.
    • If you are upgrading a version of the SAS Embedded Process before the fourth maintenance release of SAS 9.4, enter the following commands to move to the db2instancepath directory and remove the SAS directory. db2instancepath is the path to the SAS Embedded Process binary files in the DB2 instance.
      cd db2instancepath
      rm -fr SAS
    • If you are upgrading a version of the SAS Embedded Process starting with the fourth maintenance release of SAS 9.4, enter the following commands to move to the db2instancepath directory and remove the SAS directory. db2instancepath is the path to the SAS Embedded Process binary files in the DB2 instance.
      cd db2instancepath
      rm -fr SASEPHome

Installing the SAS Formats Library and Binary Files to DB2

Move the SAS Formats and Binary Files to DB2

The SAS formats library and the binary files that need to be moved to DB2 are contained in a self-extracting archive file. You can use PSFTP, SFTP, or FTP to transfer the file to the DB2 server to be unpacked and compiled.
The self-extracting archive file contains the SAS formats library and the binary files for the SAS_COMPILEUDF and SAS_DELETEUDF functions. You need these files when you want to use scoring functions to run your scoring model and when publishing SAS formats.
This self-extracting archive file is located in the YourSASDepot/SASFormatsLibraryforDB2/3.1/DB2on<AIX | Linux64>/ directory.
Choose the self-extracting archive file based on the UNIX platform that your DB2 server runs on.
  • AIX: acceldb2fmt-3.1-n_r64.sh
  • Linux(x86_64): acceldb2fmt-3.1-n_lax.sh
n is a number that indicates the latest version of the file. If this is the initial installation, n has a value of 1. Each time that you reinstall or upgrade, n is incremented by 1.
The file does not have to be downloaded to a specific location. However, you need to note where it is downloaded so that it can be executed as the DB2 instance owner at a later time. It is recommended that you put the acceldb2fmt file somewhere other than the DB2 home directory tree.
List the directory in UNIX to verify that the files have been moved.

Unpack and Install the SAS Formats Library and Binary Files for DB2

After the acceldb2fmt-3.1-n_lax.sh or acceldb2fmt-3.1-n_r64.sh self-extracting archive file is transferred to the DB2 machine, follow these steps to unpack the file. n is a number that indicates the latest version of the file. If this is the initial installation, n has a value of 1. Each time you reinstall or upgrade, n is incremented by 1.
  1. Log on as the user who owns the DB2 instance from a secured shell, such as SSH.
  2. Change to the directory where you put the acceldb2fmt file.
    cd path_to_sh_file
    path_to_sh_file is the location to which you copied the self-extracting archive file.
  3. Change permissions on the file to enable you to execute the script.
    chmod 755 -r acceldb2fmt-3.1-n_r64.sh
    Note: AIX is the platform that is being used as an example for all the steps in this topic.
  4. If there is a previously created self-extracting archive file in the SAS directory, you must either rename or remove the directory. These are examples of the commands that you would use:
    mv SAS to SAS_OLD /* renames the SAS directory */
    rm -fr SAS /* removes the SAS directory */
  5. Use the following commands to unpack the appropriate self-extracting archive file.
    ./sh_file
    sh_file is either acceldb2fmt-3.1-n_lax.sh or acceldb2fmt-3.1-n_r64.sh depending on your platform.
    After this script is run and the files are unpacked, a SAS tree is built in the current directory. The content of the target directories should be similar to the following, depending on your operating system. Part of the directory path is shaded to emphasize the different target directories that are used.
    /path_to_sh_file/SAS/SASFormatsLibraryForDB2/3.1-n/bin/InstallAccelDB2Fmt.sh
    /path_to_sh_file/SAS/SASFormatsLibraryForDB2/3.1-n/bin/CopySASFiles.sh
    /path_to_sh_file/SAS/SASFormatsLibraryForDB2/3.1-n/lib/SAS_CompileUDF
    /path_to_sh_file/SAS/SASFormatsLibraryForDB2/3.1-n/lib/SAS_DeleteUDF
    /path_to_sh_file/SAS/SASFormatsLibraryForDB2/3.1-n/lib/libjazxfbrs.so
    /path_to_sh_file/SAS/SASFormatsLibraryForDB2/3.1 ->3.1-n
  6. Use the following command to place the files in the DB2 instance:
    path_to_sh_file/SAS/SASFormatsLibraryForDB2/3.1-n/bin/
       CopySASFiles.sh db2instancepath/sqllib
    
    db2instancepath/sqllib is the path to the sqllib directory of the DB2 instance that you want to use.
    After this script is run and the files are copied, the target directory should look similar to this.
    db2instancepath/sqllib/function/SAS/SAS_CompileUDF
    db2instancepath/sqllib/function/SAS/SAS_DeleteUDF
    db2instancepath/sqllib/function/SAS/libjazxfbrs.so
    
    Note: If the SAS_CompileUDF, SAS_DeleteUDF, and libjazxfbrs.so files currently exist under the target directory, you must rename the existing files before you run the CopySASFiles.sh script. Otherwise, the CopySASFiles.sh script does not work, and you get a "Text file is busy" message for each of the three files.
  7. Use the DB2SET command to tell DB2 where to find the 64-bit formats library.
    db2set DB2LIBPATH=db2instancepath/sqllib/function/SAS
    db2instancepath/sqllib is the path to the sqllib directory of the DB2 instance that you want to use.
    The DB2 instance owner must run this command for it to be successful. Note that this is similar to setting a UNIX system environment variable using the UNIX EXPORT or SETENV commands. DB2SET registers the environment variable within DB2 only for the specified database server.
  8. To verify that DB2LIBPATH was set appropriately, run the DB2SET command without any parameters.
    db2set
    The results should be similar to this one if it was set correctly.
    DB2LIBPATH=db2instancepath/sqllib/function/SAS

Installing the SAS Embedded Process

Unzipping the SAS Embedded Process for DB2

The SAS Embedded Process is contained in a self-extracting archive file named sepcoredb2a-12.00000-1.sh (AIX) or sepcoredb2l-12.00000-1.sh (Linux). This file is contained in a ZIP file that is put in your SAS Software Depot directory. You need this file if you want to run your scoring model.
To unzip the SAS Embedded Process file for DB2:
  1. Create a new temporary directory on your client machine such as /sasep. The new directory is referred to as EPZipDir throughout this section.
  2. Navigate to the YourSASDepot/standalone_installs directory. This directory was created when you created your SAS Software Depot.
  3. Locate the en_sasexe.zip file. The en_sasexe.zip file is located in the YourSASDepot/standalone_installs/SAS_Core_Embedded_Process_Package_for_DB2on<AIX | Linux>/12_0/DB2on<AIX | Linux_x64> directory.
    The sepcoredb2a-12.00000-1.sh (AIX) or sepcoredb2l-12.00000-1.sh (Linux) file is included in this ZIP file.
  4. Copy the en_sasexe.zip file to your EPZipDir temporary directory on the client machine.
    cp en_sasexe.zip /EPZipDir
  5. Navigate to your EPZipDir temporary directory and unzip en_sasexe.zip.
    After the file is unzipped, a sasexe directory is created in the same location as the en_sasexe.zip file. The self-extracting archive file is in the /EPZipDir/sasexe directory.
The self-extracting archive file depends on the UNIX platform that your DB2 server runs on.
  • AIX: sepcoredb2a-12.00000-1.sh
  • Linux (x86_64): sepcoredb2l-12.00000-1.sh

Unpacking the SAS Embedded Process Files for DB2

After the sepcoredb2a-12.00000-1.sh (AIX) or the sepcoredb2l-12.00000-1.sh (Linux) self-extracting archive file has been transferred to the DB2 machine, follow these steps to unpack the file:
  1. Log on as the user who owns the DB2 instance from a secured shell, such as SSH.
  2. Move the self-extracting archive file to the instance owner’s home directory. This directory is referred to as path_to_sh_file.
    1. Move to the /EPZipDir/sasexe directory on the client machine where the self-extracting archive file exists.
    2. Use PSFTP, SFTP, or FTP to transfer the self-extracting archive file to the instance owner’s home directory on the DB2 server to be unpacked and compiled.
  3. Change to the directory where you put the sepcoredb2a-12.00000-1.sh (AIX) or the sepcoredb2l-12.00000-1.sh (Linux) file.
    cd path_to_sh_file
    path_to_sh_file is the location to which you copied the self-extracting archive file. This must be the instance owner home directory.
    List the directory in UNIX to verify that the file has been moved.
  4. Change permissions on the file to enable you to execute the script.
    chmod 755 -r sepcoredb2*.sh
  5. If there is a previously created SAS directory, you must either rename or remove the directory. These are examples of the commands that you would use:
    • If you are upgrading from a version of the SAS Embedded Process before the fourth maintenance release of SAS 9.4, here is an example of the commands that you would use:
      mv SAS to SAS_OLD /* renames the SAS directory */
      rm -fr SAS /* removes the SAS directory */
      
    • If you are upgrading from a version of the SAS Embedded Process from the fourth maintenance release of SAS 9.4 or later, here is an example of the commands that you would use:
      mv SASEPHome to SAS_OLD /* renames the SASEPHome directory */
      rm -fr SASEPHome /* removes the SASEPHome directory */
  6. Use the following commands to unpack the appropriate self-extracting archive file:
    ./sh_file
    sh_file is either sepcoredb2a-12.00000-1.sh or sepcoredb2l-12.00000-1.sh, depending on your platform.
    After this script is run and the files are unpacked, a SAS tree is built in the current directory. The target directories should be similar to the following, depending on your operating system. Part of the directory path is shaded to emphasize the different target directories that are used.
    /db2instancepath/SASEPHome/bin
    /db2instancepath/SASEPHome/misc
    /db2instancepath/SASEPHome/sasexe
    /db2instancepath/SASEPHome/utilities
  7. Use the DB2SET command to enable the SAS Embedded Process in DB2 and to tell the SAS Embedded Process where to find the SAS Embedded Process library files.
    dbset DB2_SAS_SETTINGS="ENABLE_SAS_EP:true;
    LIBRARY_PATH:db2instancepath/SASEPHome/sasexe"
    The DB2 instance owner must run this command for it to be successful. Note that this is similar to setting a UNIX system environment variable using the UNIX EXPORT or SETENV commands. DB2SET registers the environment variable within DB2 only for the default database instance.
    For more information about all of the arguments that can be used with the DB2SET command for the SAS Embedded Process, see DB2SET Command Syntax for the SAS Embedded Process.
  8. To verify that the SAS Embedded Process is set appropriately, run the DB2SET command without any parameters.
    db2set
    The path should be similar to this one if it was set correctly. Note that the DB2LIBPATH that was set when you installed the SAS Formats Library and binary files is also listed.
    DB2_SAS_SETTINGS=ENABLE_SAS_EP:true;
       LIBRARY_PATH:db2instancepath/SASEPHome/sasexe
       DB2LIBPATH=db2instancepath/sqllib/function/SAS
  9. Stop the database manager instance if it is not stopped already.
    db2stop
    A message indicating that the stop was successful displays.
    If the database manager instance cannot be stopped because application programs are still connected to databases, use the FORCE APPLICATION command to disconnect all users, use the TERMINATE command to clear any background processes, and then use the DB2STOP command.
    db2 list applications
    db2 force applications all
    db2 terminate
    db2stop
    
  10. (AIX only) Clear the cache.
    su root
    slibclean
    exit
  11. Restart the database manager instance.
    db2start
  12. Verify that the SAS Embedded Process started.
    ps -ef | grep db2sasep
    If the SAS Embedded Process was started, lines similar to the following are displayed:
    db2v10 23265382 20840668   0   Oct 06      -  4:03 db2sasep 
    db2v10 27983990 16646196   1 08:24:09 pts/10  0:00 grep db2sasep
    
    In the DB2 instance, you can also verify if the SAS Embedded Process log file was created in the DB2 instance’s diagnostic directory.
    cd  instance-home/sqllib/db2dump
    ls –al sasep0.log 

DB2SET Command Syntax for the SAS Embedded Process

The syntax for the DB2SET command is shown below.
DB2SET DB2_SAS_SETTINGS="
ENABLE_SAS_EP:TRUE | FALSE;
<LIBRARY_PATH:path>
<COMM_BUFFER_SZ:size;>
<COMM_TIMEOUT:timeout;>
<RESTART_RETRIES:number-of-tries;>
<DIAGPATH:path;>
<DIAGLEVEL:level-number;>"
Arguments

ENABLE_SAS_EP:TRUE | FALSE

specifies whether the SAS Embedded Process is started with the DB2 instance.

Default FALSE

LIBRARY_PATH:path

specifies the path from which the SAS Embedded Process library is loaded.

Requirement The path must be fully qualified.

COMM_BUFFER_SZ:size

specifies the size in 4K pages of the shared memory buffer that is used for communication sessions between DB2 and SAS.

Default ASLHEAPSZ dbm configuration value
Range 1–32767
Requirement size must be an integer value.

COMM_TIMEOUT:timeout

specifies a value in seconds that DB2 uses to determine whether the SAS Embedded Process is non-responsive when DB2 and SAS are exchanging control messages.

Default 600 seconds
Note If the time-out value is exceeded, DB2 forces the SAS Embedded Process to stop in order for it to be re-spawned.

RESTART_RETRIES:number-of-tries

specifies the number of times that DB2 attempts to re-spawn the SAS Embedded Process after DB2 has detected that the SAS Embedded Process has terminated abnormally.

Default 10
Range 1–100
Requirement number-of-tries must be an integer value.
Note When DB2 detects that the SAS Embedded Process has terminated abnormally, DB2 immediately attempts to re-spawn it. This argument limits the number of times that DB2 attempts to re-spawn the SAS Embedded Process. Once the retry count is exceeded, DB2 waits 15 minutes before trying to re-spawn it again.

DIAGPATH:path

specifies the path that indicates where the SAS Embedded Process diagnostic logs are written.

Default DIAGPATH dbm configuration value
Requirement The path must be fully qualified.

DIAGLEVEL:level-number

specifies the minimum severity level of messages that are captured in the SAS Embedded Process diagnostic logs. The levels are defined as follows.

1 SEVERE
2 ERROR
3 WARNING
4 INFORMATIONAL
Default DIAGLEVEL dbm configuration value
Range 1–4

Controlling the SAS Embedded Process for DB2

The SAS Embedded Process starts when a query is submitted. The SAS Embedded Process continues to run until it is manually stopped or the database is shut down.
The DB2IDA command is a utility that is installed with the DB2 server to control the SAS Embedded Process. The DB2IDA command enables you to manually stop and restart the SAS Embedded Process without shutting down the database. You might use the DB2IDA command to upgrade or reinstall the SAS Embedded Process library or correct an erroneous library path.
Note: DB2IDA requires IBM Fixpack 6 or later.
The DB2IDA command has the following parameters:
-provider sas
specifies the provider that is targeted by the command. The only provider that is supported is "sas".
-start
starts the SAS Embedded Process on the DB2 instance if the SAS Embedded Process is not currently running.
If the SAS Embedded Process is running, this command has no effect.
Note: Once the SAS Embedded Process is started, the normal re-spawn logic in DB2 applies if the SAS Embedded Process is abnormally terminated.
–stop
stops the SAS Embedded Process if it is safe to do so.
If the SAS Embedded Process is stopped, this command has no effect.
If any queries are currently running on the SAS Embedded Process, the db2ida -stop command fails and indicates that the SAS Embedded Process is in use and could not be stopped.
Note: DB2 does not attempt to re-spawn the SAS Embedded Process once it has been stopped with the db2ida -stop command.
-stopforce
forces the SAS Embedded Process to shut down regardless of whether there are any queries currently running on it.
If the SAS Embedded Process is stopped, this command has no effect.
If any queries are currently running on the SAS Embedded Process, those queries receive errors.
Note: DB2 does not attempt to re-spawn the SAS Embedded Process once it has been stopped with the db2ida -stopforce command.
Here are some examples of the DB2IDA command:
db2ida -provider sas -stopforce

db2ida -provider sas -start

Running the %INDB2_PUBLISH_COMPILEUDF Macro

Overview of the %INDB2_PUBLISH_COMPILEUDF Macro

The %INDB2_PUBLISH_COMPILEUDF macro publishes the following components to the SASLIB schema in a DB2 database:
  • SAS_COMPILEUDF function
    The SAS_COMPILEUDF function facilitates the %INDB2_PUBLISH_FORMATS format publishing macro and the %INDB2_PUBLISH_MODEL scoring publishing macro when you use scoring functions to run the scoring model. The SAS_COMPILEUDF function performs the following tasks:
    • compiles the format and scoring model source files into object files. This compilation occurs through the SQL interface using an appropriate compiler for the system.
    • links with the SAS formats library that is needed for format and scoring model publishing.
    • copies the object files to the db2instancepath/sqllib/function/SAS directory. You specify the value of db2instancepath in the %INDB2_PUBLISH_COMPILEUDF macro syntax.
  • SASUDF_DB2PATH and SASUDF_COMPILER_PATH global variables
    The SASUDF_DB2PATH and the SASUDF_COMPILER_PATH global variables are used when you publish the format and scoring model functions.
You have to run the %INDB2_PUBLISH_COMPILEUDF macro only one time in a given database.
The SAS_COMPILEUDF function must be published before you run the %INDB2_PUBLISH_DELETEUDF macro, the %INDB2_PUBLISH_FORMATS macro, and the %INDB2_PUBLISH_MODEL macro. Otherwise, these macros fail.
Note: To publish the SAS_COMPILEUDF function, you must have the appropriate DB2 user permissions to create and execute this function in the SASLIB schema and in the specified database. For more information, see DB2 Permissions.

%INDB2_PUBLISH_COMPILEUDF Macro Run Process

To run the %INDB2_PUBLISH_COMPILEUDF macro, follow these steps:
  1. Create a SASLIB schema in the database where the SAS_COMPILEUDF function is to be published.
    The SASLIB schema is used when publishing the %INDB2_PUBLISH_COMPILEUDF macro for DB2 in-database processing.
    You specify that database in the DATABASE argument of the %INDB2_PUBLISH_COMPILEUDF macro. For more information, see %INDB2_PUBLISH_COMPILEUDF Macro Syntax.
    The SASLIB schema contains the SAS_COMPILEUDF and SAS_DELETEUDF functions and the SASUDF_DB2PATH and SASUDF_COMPILER_PATH global variables.
  2. Start SAS and submit the following command in the Enhanced Editor or Program Editor:
    %let indconn = server=yourserver user=youruserid password=yourpwd
       database=yourdb schema=saslib;
    
    For more information, see the INDCONN Macro Variable.
  3. Run the %INDB2_PUBLISH_COMPILEUDF macro. For more information, see %INDB2_PUBLISH_COMPILEUDF Macro Syntax.
You can verify that the SAS_COMPILEUDF function and global variables have been published successfully. For more information, see Validating the Publishing of SAS_COMPILEUDF and SAS_DELETEUDF Functions and Global Variables.
After the SAS_COMPILEUDF function is published, run the %INDB2_PUBLISH_DELETEUDF publishing macro to create the SAS_DELETEUDF function. For more information, see Running the %INDB2_PUBLISH_DELETEUDF Macro.

INDCONN Macro Variable

The INDCONN macro variable provides the credentials to make a connection to DB2. You must specify the server, user, password, and database information to access the machine on which you have installed the DB2 database. You must assign the INDCONN macro variable before the %INDB2_PUBLISH_COMPILEUDF macro is invoked.
The value of the INDCONN macro variable for the %INDB2_PUBLISH_COMPILEUDF macro has this format.
SERVER=server USER=userid PASSWORD=password
DATABASE=database <SCHEMA=SASLIB>

SERVER=server

specifies the DB2 server name or the IP address of the server host. If the server name contains spaces or nonalphanumeric characters, enclose the server name in quotation marks.

Requirement The name must be consistent with how 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, see DB2 Installation and Configuration Steps.

USER=userid

specifies the DB2 user name (also called the user ID) that is used to connect to the database. If the user name contains spaces or nonalphanumeric characters, enclose the user name in quotation marks.

PASSWORD=password

specifies the password that is associated with your DB2 user ID. If the password contains spaces or nonalphabetic characters, enclose the password in quotation marks.

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. If the database name contains spaces or nonalphanumeric characters, enclose the database name in quotation marks.

Requirement The SAS_COMPILEUDF function is created as a Unicode function. If the database is not a Unicode database, then the alternate collating sequence must be configured to use identity_16bit.

SCHEMA=SASLIB

specifies SASLIB as the schema name.

Default SASLIB
Restriction The SAS_COMPILEUDF function and the two global variables (SASUDF_DB2PATH and SASUDF_COMPILER_PATH) are published to the SASLIB schema in the specified database. If a value other than SASLIB is used, it is ignored.
Requirement The SASLIB schema must be created before publishing the SAS_COMPILEUDF and SAS_DELETEUDF functions.

%INDB2_PUBLISH_COMPILEUDF Macro Syntax

Arguments

DB2PATH=db2instancepath/sqllib

specifies the parent directory that contains the function/SAS subdirectory, where all the object files are stored and defines the SASUDF_DB2PATH global variable that is used when publishing the format and scoring model functions.

Interaction db2instancepath should be the same path as the path that was specified during the installation of the SAS_COMPILEUDF binary file. For more information, see Step 3 in Unpack and Install the SAS Formats Library and Binary Files for DB2.
Tip The SASUDF_DB2PATH global variable is defined in the SASLIB schema under the specified database name.

COMPILER_PATH=compiler-path-directory

specifies the path to the location of the compiler that compiles the source files and defines the SASUDF_COMPILER_PATH global variable that is used when publishing the format and scoring model functions.

Tip The SASUDF_COMPILER_PATH global variable is defined in the SASLIB schema under the specified database name. The XLC compiler should be used for AIX, and the GGG compiler should be used for Linux.

DATABASE=database-name

specifies the name of a DB2 database to which the SAS_COMPILEUDF function is published.

Interaction: The database that you specify in the DATABASE= argument takes precedence over the database that you specify in the INDCONN macro variable. For more information, see %INDB2_PUBLISH_COMPILEUDF Macro Run Process.

ACTION=CREATE | REPLACE | DROP

specifies that the macro performs one of the following actions:

CREATE

creates a new SAS_COMPILEUDF function.

REPLACE

overwrites the current SAS_COMPILEUDF function, if a SAS_COMPILEUDF function by the same name is already registered, or creates a new SAS_COMPILEUDF function if one is not registered.

DROP

causes the SAS_COMPILEUDF function to be dropped from the DB2 database.

Default CREATE
Tip If the SAS_COMPILEUDF function was published previously and you now specify ACTION=CREATE, you receive warning messages from DB2. If the SAS_COMPILEUDF function was published previously and you specify ACTION=REPLACE, no warnings are issued.

OBJNAME=object-file-name

specifies the object filename that the publishing macro uses to register the SAS_COMPILEUDF function. The object filename is a file system reference to a specific object file, and the value entered for OBJNAME must match the name as it exists in the file system. For example, SAS_CompileUDF is mixed case.

Default SAS_CompileUDF
Interaction If the SAS_COMPILEUDF function is updated, you might want to rename the object file to avoid stopping and restarting the database. If so, the SAS_COMPILEUDF function needs to be reregistered with the new object filename.

OUTDIR=diagnostic-output-directory

specifies a directory that contains diagnostic files.

Tip Files that are produced include an event log that contains detailed information about the success or failure of the publishing process.

Running the %INDB2_PUBLISH_DELETEUDF Macro

Overview of the %INDB2_PUBLISH_DELETEUDF Macro

The %INDB2_PUBLISH_DELETEUDF macro publishes the SAS_DELETEUDF function in the SASLIB schema of a DB2 database. The SAS_DELETEUDF function facilitates the %INDB2_PUBLISH_FORMATS format publishing macro and the %INDB2_PUBLISH_MODEL scoring publishing macro. The SAS_DELETEUDF function removes existing object files when the format or scoring publishing macro registers new ones by the same name.
You have to run the %INDB2_PUBLISH_DELETEUDF macro only one time in a given database.
The SAS_COMPILEUDF function must be published before you run the %INDB2_PUBLISH_DELETEUDF macro, the %INDB2_PUBLISH_FORMATS macro, and the %INDB2_PUBLISH_MODEL macro. Otherwise, these macros fail.
Note: To publish the SAS_DELETEUDF function, you must have the appropriate DB2 user permissions to create and execute this function in the SASLIB schema and specified database. For more information, see DB2 Permissions.

%INDB2_PUBLISH_DELETEUDF Macro Run Process

To run the %INDB2_PUBLISH_DELETEUDF macro, follow these steps:
  1. Ensure that you have created a SASLIB schema in the database where the SAS_DELETEUDF function is to be published.
    Use the SASLIB schema when publishing the %INDB2_PUBLISH_DELETEUDF macro for DB2 in-database processing.
    The SASLIB schema should have been created before you ran the %INDB2_PUBLISH_COMPILEUDF macro to create the SAS_COMPILEUDF function. The SASLIB schema contains the SAS_COMPILEUDF and SAS_DELETEUDF functions and the SASUDF_DB2PATH and SASUDF_COMPILER_PATH global variables.
    The SAS_COMPILEUDF function must be published before you run the %INDB2_PUBLISH_DELETEUDF macro. The SAS_COMPILEUDF and SAS_DELETEUDF functions must be published to the SASLIB schema in the same database. For more information about creating the SASLIB schema, see %INDB2_PUBLISH_COMPILEUDF Macro Run Process.
  2. Start SAS and submit the following command in the Enhanced Editor or Program Editor.
    %let indconn = server=yourserver user=youruserid password=yourpwd
       database=yourdb schema=saslib;
    
    For more information, see the INDCONN Macro Variable.
  3. Run the %INDB2_PUBLISH_DELETEUDF macro. For more information, see %INDB2_PUBLISH_DELETEUDF Macro Syntax.
You can verify that the function has been published successfully. For more information, see Validating the Publishing of SAS_COMPILEUDF and SAS_DELETEUDF Functions and Global Variables.
After the SAS_DELETEUDF function is published, the %INDB2_PUBLISH_FORMATS and the %INDB2_PUBLISH_MODEL macros can be run to publish the format and scoring model functions.

INDCONN Macro Variable

The INDCONN macro variable provides the credentials to make a connection to DB2. You must specify the server, user, password, and database information to access the machine on which you have installed the DB2 database. You must assign the INDCONN macro variable before the %INDB2_PUBLISH_DELETEUDF macro is invoked.
The value of the INDCONN macro variable for the %INDB2_PUBLISH_DELETEUDF macro has this format.
SERVER=server USER=userid PASSWORD=password
DATABASE=database <SCHEMA=SASLIB>

SERVER=server

specifies the DB2 server name or the IP address of the server host. If the server name contains spaces or nonalphanumeric characters, enclose the server name in quotation marks.

Requirement The name must be consistent with how the host name was cached when PSFTP server was run from the command window. If the full server name was cached, use the full server name in the SERVER argument. If the short server name was cached, 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, see DB2 Installation and Configuration Steps.

USER=userid

specifies the DB2 user name (also called the user ID) that is used to connect to the database. If the user name contains spaces or nonalphanumeric characters, enclose the user name in quotation marks.

PASSWORD=password

specifies the password that is associated with your DB2 user ID. If the password contains spaces or nonalphabetic characters, enclose the password in quotation marks.

Tip Use only PASSWORD=, PASS=, or PW= for the password argument. PWD= is not supported and causes errors.

DATABASE=database

specifies the DB2 database that contains the tables and views that you want to access. If the database name contains spaces or nonalphanumeric characters, enclose the database name in quotation marks.

SCHEMA=SASLIB

specifies SASLIB as the schema name.

Default SASLIB
Restriction The SAS_DELETEUDF function is published to the SASLIB schema in the specified database. If a value other than SASLIB is used, it is ignored.
Requirement Create the SASLIB schema before publishing the SAS_COMPILEUDF and SAS_DELETEUDF functions.

%INDB2_PUBLISH_DELETEUDF Macro Syntax

Arguments

DATABASE=database-name

specifies the name of a DB2 database to which the SAS_DELETEUDF function is published.

Interaction The database that you specify in the DATABASE argument takes precedence over the database that you specify in the INDCONN macro variable. For more information, see Running the %INDB2_PUBLISH_DELETEUDF Macro.

ACTION=CREATE | REPLACE | DROP

specifies that the macro performs one of the following actions:

CREATE

creates a new SAS_DELETEUDF function.

REPLACE

overwrites the current SAS_DELETEUDF function, if a SAS_DELETEUDF function by the same name is already registered, or creates a new SAS_DELETEUDF function if one is not registered.

DROP

causes the SAS_DELETEUDF function to be dropped from the DB2 database.

Default CREATE
Tip If the SAS_DELTEUDF function was published previously and you specify ACTION=CREATE, you receive warning messages from DB2. If the SAS_DELETEUDF function was published previously and you specify ACTION=REPLACE, no warnings are issued.

OUTDIR=diagnostic-output-directory

specifies a directory that contains diagnostic files.

Tip Files that are produced include an event log that contains detailed information about the success or failure of the publishing process.
Last updated: February 9, 2017