How to pass arguments to C, C++, OLE, or COBOL routines

In addition to the SQL arguments that are specified in the DML reference for a routine, the database manager pass additional arguments to the external routine body. The nature and order of these arguments is determined by the parameter style with which you registered your routine.

To ensure that information is exchanged correctly between invokers and the routine body, you must ensure that your routine accepts arguments in the order they are passed, according to the parameter style that is being used. The sqludf include file can aid you in handling and using these arguments.

The following parameter styles are applicable only to LANGUAGE C, LANGUAGE OLE, and LANGUAGE COBOL routines.

PARAMETER STYLE SQL routines

PARAMETER STYLE DB2SQL procedures

PARAMETER STYLE GENERAL procedures

PARAMETER STYLE GENERAL WITH NULLS procedures

Note: For UDFs and methods, PARAMETER STYLE SQL is equivalent to PARAMETER STYLE DB2SQL.

The arguments for the PARAMETER STYLE SQL, PARAMETER STYLE GENERAL, and PARAMETER STYLE GENERAL WITH NULL are described as follows:

SQL-argumentEach SQL-argument argument represents one input or output value that is defined when the routine was created. The list of arguments is determined as follows:

For a scalar function, one argument for each input parameter to a function followed by one SQL-argument argument for the result of a function.
For a table function, one argument for each input parameter to a function followed by one SQL-argument argument for each column in the result table of a function.
For a method, one SQL-argument argument for the subject type of a method, then one argument for each input parameter to a method followed by one SQL-argument for the result of a method.
For a stored procedure, one SQL-argument argument for each parameter to a stored procedure.

Each SQL-argument argument is used as follows:

Input parameter of a function or method, subject type of a method, or an IN parameter of a stored procedure.
The input argument is set by the database manager before the routine is called. The value of each of these arguments is taken from the expression that is specified in the routine invocation. It is expressed in the data type of the corresponding parameter definition in the CREATE statement.
Result of a function, method, or an OUT parameter of a stored procedure.
The output argument is set by a routine before it is returned to the database manager. The database manager allocates the buffer and passes its address to a routine. The routine puts the result value into the buffer. Enough buffer space is allocated by the database manager to contain the value that is expressed in the data type. For character types and LOBs, the maximum size as defined in the create statement is allocated.

For scalar functions and methods, the result data type is defined in the CAST FROM clause. If the CAST FROM clause is not present, then the result data type is defined in the RETURNS clause.

For table functions, the database manager defines a performance optimization where every defined column does not have to be returned to the database manager. If you write your UDF to take advantage of this feature, it returns only the columns that are required by the statement, which references the table function. For example, consider a CREATE FUNCTION statement for a table function that is defined with 100 result columns. If a statement that references the function is only interested in two columns, optimization by the database manager enables the UDF to return only those two columns for each row and not spend time on the other 98 columns. For more information, see the dbinfo argument content.

For each value returned, the routine returns bytes that are required for the data type and length of the result. Maximums are defined during the creation of the routine's catalog entry. An overwrite by the routine can cause unpredictable results or an abnormal termination.
INOUT parameter of a stored procedure.
The INOUT argument behaves as both an IN and an OUT parameter and therefore follows both sets of rules that are shown previously. The database manager sets the argument before a stored procedure is called. The buffer for the argument is allocated by the database manager based on the maximum size of the parameter that is defined in the CREATE procedure statement. For example, an INOUT parameter of a CHAR type might have a 10 byte VARCHAR going in to the stored procedure, and a 100 byte VARCHAR coming out of the stored procedure. The buffer is set by the stored procedure before it is returned to the database manager.

SQL-argument-indThe SQL-argument-ind argument corresponds to each SQL-argument value that is passed to a routine. The nth SQL-argument-ind argument corresponds to the nth SQL-argument value and indicates whether the SQL-argument argument contains a NULL value.

Each SQL-argument-ind is used as follows:

Input parameter of a function or method, subject type of a method, or an IN parameter of a stored procedure:
The input argument is set by the database manager before a routine is called. It contains one of the following values:
0
The argument is present and it is not NULL.
-1
The argument is present and its value is NULL.

If the routine is defined with the RETURNS NULL ON NULL INPUT option, the routine body does not need to check for a NULL value. However, if the routine is defined with the CALLED ON NULL INPUT option, any argument can be NULL and the routine must check the SQL-argument-ind value before it uses the corresponding SQL-argument value.
Result of a function, method, or an OUT parameter of a stored procedure:
The output argument is set by the routine before it is returned to the database manager. The SQL-argument-ind argument is used by the routine to signal if the particular result value is NULL:
0
The result is not NULL.
-1
The result is the NULL value.

Even if the routine is defined with the RETURNS NULL ON NULL INPUT option, the routine body must set the SQL-argument-ind value of the result. For example, a divide function might set the result to null when the denominator is zero.
For scalar functions and methods, the database manager treats a NULL result as an arithmetic error if the following conditions are true:
- The database configuration parameter dft_sqlmathwarn is set to YES.
- One of the input arguments is a null because of an arithmetic error.
The database manager treats NULL results as an arithmetic error if you define the function with the RETURNS NULL ON NULL INPUT option.

For table functions that use the result column list to optimize performance, only the indicators corresponding to the required columns need be set.
INOUT parameter of a stored procedure:
The INOUT argument behaves as both an IN and an OUT parameter and therefore follows rules for both IN and OUT parameters. The database manager sets the argument before a stored procedure is called. The SQL-argument-ind is set by the stored procedure before it is returned to the database manager.

Each SQL-argument-ind value is in form of SMALLINT.

SQL-argument-ind-arrayThe SQL-argument-ind-array argument is array of elements where each element corresponds to each SQL-argument value that is passed to a stored procedure. The nth element in SQL-argument-ind-array argument corresponds to the nth SQL-argument value and indicates whether the SQL-argument argument contains a NULL value.

Each element in the SQL-argument-ind-array argument is used as follows:

IN parameter of a stored procedure:
The IN parameter element is set by the database manager before a routine is called. It contains one of the following values:

0
The argument is present and not NULL.
-1
The argument is present and its value is NULL.

If the stored procedure is defined with the RETURNS NULL ON NULL INPUT option, the stored procedure body does not need to check for a NULL value. However, if the stored procedure is defined with the CALLED ON NULL INPUT option, any argument can be NULL. If your stored procedure is defined with the CALLED ON NULL INPUT option, your stored procedure must check the SQL-argument-ind value before it uses the corresponding SQL-argument value.
OUT parameter of a stored procedure:
The OUT parameter element is set by the routine before it is returned to the database manager. The SQL-argument-ind-array argument is used by the routine to signal if the particular result value is NULL:

0 or positive

The result is not NULL.

negative

The result is the NULL value.
INOUT parameter of a stored procedure:
The INOUT parameter element behaves as both an IN and an OUT parameter. The INOUT parameters must follow rules for both IN and OUT parameters. The database manager sets the argument before a stored procedure is called. The element of SQL-argument-ind-array is set by the stored procedure before it is returned to the database manager.

Each element of SQL-argument-ind-array value is in form of SMALLINT.

sqlstateThe sqlstate argument is set by the routine before it is returned to the database manager. The sqlstate argument can be used by the routine to signal warning or error conditions. The routine can set this argument to any value. The value '00000' means that no warning or error situations were detected. Values that start with '01' are warning conditions. Values that start with anything other than '00' or '01' are error conditions. When the routine is called, the argument contains the value '00000'.

For error conditions, the routine returns an SQLCODE of -443. For warning conditions, the routine returns an SQLCODE of +462. If the SQLSTATE is 38001 or 38502, then the SQLCODE is -487.

The sqlstate value is in form of CHAR(5).

routine-nameThe routine name argument is set by the database manager before a routine is called. It is the qualified function name that is passed from the database manager to a routine.

The routine-name is passed in the following format:

   schema.routine

The routine name can contain a schema name and a routine name, which are separated by a period. The following example contains two routine names.

   PABLO.BLOOP   WILLIE.FINDSTRING

You can use the schema.routine form to have same routines under a different schema name.

Although it is possible to include the period in object names and schema names, avoid use of the period in object names to avoid confusion. For example, if you have a routine name that is passed as the OBJ.OP.ROTATE value, it is difficult to distinguish the schema name or the routine name.

The routine-name value is in form of VARCHAR(257).

specific-nameThe specific-name argument is set by the database manager before a routine is called. It is the specific name that is passed from the database manager to a routine.

The following lists two examples of the specific name value:

    WILLIE_FIND_FEB99   SQL9904281052440430

The first example of the specific name value is provided by the user in the CREATE statement. The second example of the specific name value is generated by the database manager from the current timestamp when the user does not specify the specific name value.

As with the routine-name argument, the specific name value is used to clearly identify the routine.

The specific-name value is in form of VARCHAR(18).

diagnostic-messageThe diagnostic-message argument is set by the routine before it is returned to the database manager. The routine can use the diagnostic-message argument to insert a message text in a database message.

Routines can include descriptive information in the diagnostic-message argument with the sqlstate argument when an error or a warning is encountered. The database manager includes this information as a token in its message.

The database manager sets the first character to null before a routine is called. The diagnostic message that is returned by the routine is treated as a C null-terminated string. The diagnostic message string is included in the SQLCA structure as a token for the error condition. The first part of the diagnostic message string appears in the SQLCA or CLP message. However, the actual number of characters that appear depends on the lengths of the other tokens. The database manager might truncate the tokens to conform to the total token length limit that is imposed by the SQLCA structure. Avoid use of X'FF' in the diagnostic message string. The X'FF' value is used to delimit tokens in the SQLCA structure.

You must ensure that the routine only returns texts that fits in the VARCHAR(70) buffer that is passed to it. An overwrite by the routine can cause unpredictable results or an abend.

The database manager assumes that any message tokens returned from the routine to the database manager are in the same code page as the routine. You must ensure that the code page that is set in the database manager is same as the routine. If you use the 7-bit invariant ASCII subset, your routine can return the message tokens in any code page.

The diagnostic-message value is in form of VARCHAR(70).

scratchpadThe scratchpad argument is set by the database manager before an UDF or a method is called. It is only present for functions and methods that specified the SCRATCHPAD keyword during registration. The scratchpad argument is a structure, which is similar to the sqllob data structure and contains the following elements:

An integer field that contains the length of the scratchpad. Changing the length of the scratchpad results in SQLCODE -450 (SQLSTATE 39501)
The actual scratchpad that is initialized to all zero binary value as listed in the following scenarios:
- For scalar functions and methods, the scratchpad is initialized before the first call, and not read or modified by the database manager thereafter.
- For table functions, the scratchpad is initialized prior to the FIRST call to an UDF if the FINAL CALL option is specified in the CREATE FUNCTION statement. After the FIRST call, the scratchpad content is totally under control of the table function. If an option other than FINAL CALL is specified, the scratchpad is initialized for each OPEN call and the scratchpad content is completely under control of the table function between OPEN calls. The scratchpad initialization behavior for a UDF with the FINAL CALL option that is specified in the CREATE function statement is important for a table function that is used in a join or subquery. If it is necessary to maintain the content of the scratchpad across OPEN calls, then the FINAL CALL option must be specified in your CREATE FUNCTION statement. When the FINAL CALL option is specified, the table function also receives the FIRST and FINAL calls in addition to the normal OPEN, FETCH, and CLOSE calls for scratchpad maintenance and resource release.

The scratchpad can be mapped in your routine using the same type as either a CLOB or a BLOB, as the argument passed has the same structure.

Ensure that your routine code does not write outside of the scratchpad buffer. An overwrite by the routine can cause unpredictable results that include an abend.

If a scalar UDF or method that uses a scratchpad is referenced in a subquery, the database manager might decide to refresh the scratchpad between invocations of the subquery. If the FINAL CALL option is specified for an UDF, the scratchpad is refreshed after a final-call is made.

call-typeThe call-type argument, if present, is set by the database manager before an UDF or a method is called. The call-type argument is present for the all UDFs and methods that are registered with the FINAL CALL option.

You can avoid future exceptions that can arise as the result of added new call types if your UDF or method contains a switch or a case statement that explicitly test for all the expected values.

Your routine can set the sqlstate and diagnostic-message argument for all call-type values.

The include file sqludf.h is intended for use with routines. The sqludf.h file contains symbolic define statements for the following call-type values:

The following call-type values can be specified for scalar functions and methods:

SQLUDF_FIRST_CALL (-1)

The SQLUDF_FIRST_CALL value indicates the FIRST call to the routine for a statement. If any scratchpad is specified with the scratchpad argument, the scratchpad is set to binary zeros when the routine is called. All argument values are passed, and the routine performs one-time initialization actions that are required. The routine is expected to return the result with the sqlstate and diagnostic-message information.

If the scratchpad argument is specified without the FINAL CALL option in the CREATE FUNCTION statement, the routine cannot use the SQLUDF_FIRST_CALL call type to identify the first call. A routine that has the scratchpad argument that is specified without the FINAL CALL option in the CREATE FUNCTION statement must rely on all-zero state in the scratchpad to identify the first call.

SQLUDF_NORMAL_CALL (0)

The SQLUDF_NORMAL_CALL value indicates a normal call. All the SQL input values are passed, and the routine is expected to return the result. The routine can also return sqlstate and diagnostic-message information.

SQLUDF_FINAL_CALL (1)

The SQLUDF_FINAL_CALL value indicates a final call where no SQL-argument or SQL-argument-ind values are passed, and attempts to examine these values can cause unpredictable results. If a scratchpad is also passed, it is untouched from the previous call. The routine is expected to release resources.

SQLUDF_FINAL_CRA (255)

The SQLUDF_FINAL_CRA value indicates a final call that is identical to the SQLUDF_FINAL_CALL call type, but the SQLUDF_FINAL_CRA call type is made to routines that are defined as being able to issue SQL statements. The SQLUDF_FINAL_CRA call type is made when the routine must not issue any SQL statements other than CLOSE cursor. For example, when the database manager is in the middle of COMMIT processing, it cannot issue any new SQL statement, and any FINAL call that is issued to a routine would be a call with the SQLUDF_FINAL_CRA call type. Routines that are not defined as containing any SQL statements do not receive the SQLUDF_FINAL_CRA call type, whereas routines that contain SQL statements can receive either the SQLUDF_FINAL_CALL or SQLUDF_FINAL_CRA call types.

A scalar UDF or method is expected to release resources that were allocated. For example, the resource can include memory. When the SQLUDF_FINAL_CALL or SQLUDF_FINAL_CRA call type is specified, resources that were allocated for the routine execution are released if the scratchpad is also specified and is used to track the resource. If the FINAL CALL option is not specified with the CREATE statement, allocated resources for a routine must be released when the call statement is completed.

The following call-type values can be specified for table functions:

SQLUDF_TF_FIRST (-2)

The SQLUDF_TF_FIRST value indicates the first call, which occurs only if the FINAL CALL option was specified for the UDF. The scratchpad is set to binary zeros before a call is made with the SQLUDF_TF_FIRST call type. All argument values are passed to the table function. The table function can acquire memory or perform other one-time only resource initialization. The call with the SQLUDF_TF_FIRST call type is not an open call. An open call follows this call. On a first call, the table function must not return any data to the database manager as the database manager ignores the data.

SQLUDF_TF_OPEN (-1)

The SQLUDF_TF_OPEN value indicates the open call. The scratchpad is initialized if the NO FINAL CALL option is specified, but not necessarily otherwise. All SQL argument values are passed to the table function. The table function must not return any data to the database manager on the open call.

SQLUDF_TF_FETCH (0)

The SQLUDF_TF_FETCH value indicates a fetch call, and the database manager expect the table function to return either of the following values:

A row that contains the set of return values.
An end-of-table condition value that is indicated by SQLSTATE 02000.

If a scratchpad is also passed, it is untouched from the previous call.

SQLUDF_TF_CLOSE (1)

The SQLUDF_TF_CLOSE value indicates a close call to the table function. The SQLUDF_TF_CLOSE call type can be specified to close external resources such as source file, and release resources when the NO FINAL CALL option is specified.

In cases that involve a join or a subquery, the open, fetches, and close call sequences can repeat within the execution of a statement. However, there can be only one first call and only one final call. The first and final calls occur only when the FINAL CALL option is specified for the table function.

SQLUDF_TF_FINAL (2)

The SQLUDF_TF_FINAL value indicates a final call. The SQLUDF_TF_FINAL call type is specified only when the FINAL CALL option was specified for the table function. The SQLUDF_TF_FINAL call type can occur only once per execution of a statement. It is intended for releasing resources.

SQLUDF_TF_FINAL_CRA (255)

The SQLUDF_TF_FINAL_CRA value indicates a final call that is identical to the SQLUDF_TF_FINAL call type, but the SQLUDF_TF_FINAL_CRA call type is made to UDFs that are defined as being able to SQL statements. The SQLUDF_TF_FINAL_CRA call type is made when the UDF must not issue any SQL statements other than CLOSE cursor. For example, when the database manager is in the middle of COMMIT processing, it cannot issue any new SQL statement, and any FINAL call that is issued to a UDF would be a call with the SQLUDF_TF_FINAL_CRA call type. UDFs that are not defined as containing any SQL statements do not receive the SQLUDF_TF_FINAL_CRA call type, whereas UDFs that contain SQL statements can receive either the SQLUDF_TF_FINAL or SQLUDF_TF_FINAL_CRA call types.

You must ensure that your routine releases all resources it allocates. For table functions, you can release the resources on the close call and the final call. The close call can occur multiple times in the execution of a statement while the final call can occur only once in a statement if the FINAL CALL option was specified. You can optimize the resource usage by allocating the resource during a call with the SQLUDF_TF_FIRST call type and releasing the resources on a call with the SQLUDF_TF_FINAL or SQLUDF_TF_FINAL_CRA call type. If the FINAL CALL option is specified, the scratchpad is initialized only before a call with the SQLUDF_TF_FIRST call type. If a call with the SQLUDF_TF_FINAL or SQLUDF_TF_FINAL_CRA call type is not specified, then the scratchpad is reinitialized before each call with the SQLUDF_TF_OPEN call type.

The call-type takes the form of an integer value.

dbinfoThe dbinfo argument is set by the database manager before a routine is called. It is only present if the CREATE statement for the routine specifies the dbinfo keyword. The sqludf_dbinfo structure that is defined in the header file sqludf.h is passed as the dbinfo argument. The sqludf_dbinfo structure contains variable names and identifiers that can be longer than the maximum value, which is supported by the database manager. The longer variable length is designed for compatibility with future releases. You can use the length variable that complements each name and identifier variable to read or extract the portion of the variable that is used. The sqludf_dbinfo structure contains the following elements:

Database name length (dbnamelen)
The length of a database name. This field is an unsigned short integer.
Database name (dbname)
The name of the currently connected database. This field is a long identifier of 128 characters. The dbnamelen field identifies the actual length of this field. It does not contain a null terminator or any padding.
Application Authorization ID Length (authidlen)
The length of application authorization ID. This field is an unsigned short integer.
Application authorization ID (authid)
The application runtime authorization ID. This field is a long identifier of 128 characters. It does not contain a null terminator or any padding. The authidlen field identifies the actual length of this field.
Environment code pages (codepg)
The code page information.
The environment code page is a union of three 48-byte structures:
- The cdpg_db2 structure that is common to all Db2 products.
- The cdpg_cs structure that represents older version of databases that are now deprecated.
- The cdpg_mvs structure that represents older legacy databases that are now deprecated.
You can use the cdpg_db2 structure for the codepg element. The cdpg_db2 structure is made up of an array (db2_ccsids_triplet) of three sets of code page information that represents the possible encoding schemes in the database as follows:
1. ASCII encoding scheme. For compatibility with previous version of IBM® database, if the database is a Unicode database then the information for the Unicode encoding scheme is placed here and in the third element.
2. EBCDIC encoding scheme.
3. Unicode encoding scheme.
Following the encoding scheme information is the array index of the encoding scheme for the routine (db2_encoding_scheme).
Each element of the array is composed of three fields:
- db2_sbcs: Single-byte code page, an unsigned long integer.
- db2_dbcs: Double-byte code page, an unsigned long integer.
- db2_mixed: Composite code page (also called mixed code page), an unsigned long integer.
Schema name length (tbschemalen)
The length of schema name. Contains 0 (zero) if a table name is not passed. This field is an unsigned short integer.
Schema name (tbschema)
Schema for the table name. This field is a long identifier of 128 characters. It does not contain a null terminator or any padding. The tbschemalen field identifies the actual length of this field.
Table name length (tbnamelen)
The length of the table name. Contains 0 (zero) if a table name is not passed. This field is an unsigned short integer.
Table name (tbname)
Name of the table that being updated or inserted. The tbname field is set only if the routine reference is the right-side of a SET clause in an UPDATE statement, or an item in the VALUES list of an INSERT statement. This field is a long identifier of 128 characters. It does not contain a null terminator or any padding. The table name length field identifies the actual length of this field. The schema name field with the table name field forms the fully qualified table name.
Column name length (colnamelen)
Length of column name. It contains a 0 (zero) if a column name is not passed. This field is an unsigned short integer.
Column name (colname)
Name of the column that is being updated or inserted. The colname field can contain up to 128 characters. It does not contain a null terminator or any padding. The colnamelen field identifies the actual length of this field.
Version/Release number (ver_rel)
An eight character field that identifies the product and its version, release, and modification level with the format pppvvrrm where:
- ppp identifies the product as follows:
  
  DSN
  
  Db2 for z/OS or OS/390®
  
  ARI
  
  SQL⁄DS or Db2® for VM or VSE
  
  QSQ
  
  Db2 for IBM i
  
  SQL
  
  Db2
- vv is a two-digit version identifier.
- rr is a two-digit release identifier.
- m is a one-digit modification level identifier.
Reserved field (resd0)
The reserved field is for future use.
Platform (platform)
The operating system (platform) for the application server, as follows:

SQLUDF_PLATFORM_AIX

AIX®

SQLUDF_PLATFORM_LINUX

Linux®

SQLUDF_PLATFORM_ZOS

Db2 for z/OS

SQLUDF_PLATFORM_WINDOWS

Windows operating systems

SQLUDF_PLATFORM_UNKNOWN

Unknown operating system or platform

For operating systems that are not contained in the preceding list, see the contents of the sqludf.h file.
Number of table function column list entries (numtfcol)
The number of column entries in the result column list of the table function.
Reserved field (resd1)
The reserved field is for future use.
Routine id of the stored procedure that invoked the current routine (procid)
The stored procedure's routine id matches the ROUTINEID column in the SYSCAT.ROUTINES catalog view, which can be used to retrieve the name of the invoking stored procedure. This field is a 32-bit signed integer.
Reserved field (resd2)
This field is for future use.
Table function column list (tfcolumn)
If the routine is a table function, the tfcolumn field is a pointer to an array of short integers that is dynamically allocated by the database manager. If a routine is not a table function, the tfcolumn pointer is null.

The tfcolumn field is used only for table functions. Only the first n entries, where n is a value that is specified in the numtfcol field, are relevant. The n value must be equal or greater than 0, and it must be equal or less than the number of result columns that are defined for the RETURNS TABLE(...) clause in the CREATE FUNCTION statement. Each element value in the array corresponds to an ordinal number of a result column from the table function. Order of the array elements is arranged in the order that is required by the statement. For example, a value of '1' means the first defined result column, '2' means the second defined result column. The ordinal number value of a result column can be in any order. If no actual result column values from the table function are needed by the statement, the n value and the numtfcol variable value is zero.

The use of the tfcolumn field optimizes the performance since UDF is not required to return all the result columns from the table function.
Unique application identifier (appl_id)
The unique application identifier field is a pointer to a C null-terminated string that uniquely identifies the application's connection to a database. It is generated by the database manager at connect time.
The string has a maximum length of 32 characters in the following format:
```
   x.y.ts
```
where the x and y values vary by connection type, but the ts value represents a 12 character time stamp value of the form YYMMDDHHMMSS. The ts value ensures uniqueness of the string.
```
   Example:  *LOCAL.db2inst.980707130144
```
Reserved field (resd3)
This field is for future use.