DSN8DUTI
Returns the name or the schema name or the location name of an alias according to the name of the UDF and the number of input parameters passed, as follows.
/********************************************************************* 00010000
* Module name = DSN8DUTI (DB2 sample program) * 00020000
* * 00030000
* DESCRIPTIVE NAME = Resolve a fully-qualified (3 part), partially- * 00040000
* qualified (2 part), or unqualified alias to the * 00050000
* name, schema, or location of its base table or * 00060000
* view. * 00070000
* * 00080000
* LICENSED MATERIALS - PROPERTY OF IBM * 00090000
* 5675-DB2 * 00100000
* (C) COPYRIGHT 1997, 2000 IBM CORP. ALL RIGHTS RESERVED. * 00110000
* * 00150000
* STATUS = VERSION 7 * 00160000
* * 00190000
* Function: Returns the name or the schema name or the location name * 00250000
* of an alias according to the name of the UDF and the * 00260000
* number of input parameters passed, as follows: * 00270000
* * 00280000
* TABLE_NAME( objectname ) * 00290000
* returns the unqualified name of the object found after * 00300000
* any alias chains have been resolved. The specified * 00310000
* object name, the default schema, and a location name * 00320000
* of "%" (for any location) are used as the starting * 00330000
* point of the resolution. If the starting point does * 00340000
* not refer to an alias, the unqualified name of the * 00350000
* starting point is returned. The resulting name may be * 00360000
* of a table, view, or undefined object. * 00370000
* * 00380000
* TABLE_NAME( objectname, objectschema ) * 00390000
* returns the unqualified name of the object found after * 00400000
* any alias chains have been resolved. The specified * 00410000
* object name and schema, and a location name of "%" * 00420000
* (for any location), are used as the starting point of * 00430000
* the resolution. If the starting point does not refer * 00440000
* to an alias, the unqualified name of the starting * 00450000
* point is returned. The resulting name may be of a * 00460000
* table, view, or undefined object. * 00470000
* * 00480000
* TABLE_NAME( objectname, objectschema, objectlocation ) * 00490000
* returns the unqualified name of the object found after * 00500000
* any alias chains have been resolved. The specified * 00510000
* object name, schema, and location name are used as the * 00520000
* starting point of the resolution. If the starting * 00530000
* point does not refer to an alias, the unqualified name * 00540000
* of the starting point is returned. The resulting name * 00550000
* may be of a table, view, or undefined object. * 00560000
* * 00570000
* TABLE_SCHEMA( objectname ) returns the schema name of * 00580000
* the object found after any alias chains have been * 00590000
* resolved. The specified object name, the default * 00600000
* schema, and a location name of "%" (for any location) * 00610000
* are used as the starting point of the resolution. If * 00620000
* the starting point does not refer to an alias, the * 00630000
* schema name of the starting point is returned. The * 00640000
* resulting schema name may be of a table, view, or * 00650000
* undefined object. * 00660000
* * 00670000
* TABLE_SCHEMA( objectname, objectschema ) returns the * 00680000
* schema name of the object found after any alias chains * 00690000
* have been resolved. The specified object name and * 00700000
* schema and a location name of "%" (for any location) * 00710000
* are used as the starting point of the resolution. If * 00720000
* the starting point does not refer to an alias, the * 00730000
* schema name of the starting point is returned. The * 00740000
* resulting schema name may be of a table, view, or * 00750000
* undefined object. * 00760000
* * 00770000
* TABLE_SCHEMA( objectname, objectschema, objectlocation ) * 00780000
* returns the schema name of the object found after any * 00790000
* alias chains have been resolved. The specified object * 00800000
* name, schema, and location name are used as the * 00810000
* starting point of the resolution. If the starting * 00820000
* point does not refer to an alias, the schema name of * 00830000
* starting point is returned. The resulting schema name * 00840000
* may be of a table, view, or undefined object. * 00850000
* * 00860000
* TABLE_LOCATION( objectname ) returns the location name * 00870000
* of the object found after any alias chains have been * 00880000
* resolved. The specified object name, the default * 00890000
* schema, and a location name of "%" (for any location) * 00900000
* are used as the starting point of the resolution. If * 00910000
* the starting point does not refer to an alias, a blank * 00920000
* location name (indicating the current server) is * 00930000
* returned. The resulting location name may be of a * 00940000
* table, view, or undefined object. * 00950000
* * 00960000
* TABLE_LOCATION( objectname, objectschema ) returns the * 00970000
* location name of the object found after any alias * 00980000
* chains have been resolved. The specified object name, * 00990000
* schema, and a location name of "%" (for any location) * 01000000
* are used as the starting point of the resolution. If * 01010000
* the starting point does not refer to an alias, a blank * 01020000
* location name (indicating the current server) is * 01030000
* returned. The resulting location name may be of a * 01040000
* table, view, or undefined object. * 01050000
* * 01060000
* TABLE_LOCATION( objectname,objectschema,objectlocation ) * 01070000
* returns the location name of the object found after * 01080000
* any alias chains have been resolved. The specified * 01090000
* object name, schema, and location name are used as the * 01100000
* starting point of the resolution. If the starting * 01110000
* point does not refer to an alias, a blank location * 01120000
* name (indicating the current server) is returned. The * 01130000
* resulting location name may be of a table, view, or * 01140000
* undefined object. * 01150000
* * 01160000
* Notes: * 01170000
* Dependencies: Requires IBM C/C++ for OS/390 V1R3 or higher * 01180000
* * 01190000
* Restrictions: * 01200000
* * 01210000
* Module type: C program * 01220000
* Processor: IBM C/C++ for OS/390 V1R3 or subsequent release * 01230000
* Module size: See linkedit output * 01240000
* Attributes: Re-entrant and re-usable * 01250000
* * 01260000
* Entry Point: CEESTART (Language Environment entry point) * 01270000
* Purpose: See Function * 01280000
* Linkage: DB2SQL * 01290000
* Invoked via SQL UDF call * 01300000
* * 01310000
* Parameters: DSN8DUTI uses the C "main" argument convention of * 01320000
* argv (argument vector) and argc (argument count). * 01330000
* * 01340000
* The location of input and output parameters depends * 01350000
* on whether the UDF (TABLE_NAME, TABLE_SCHEMA, or * 01360000
* TABLE_SCHEMA) is invoked with one, two, or three * 01370000
* input arguments. * 01380000
* * 01390000
* If the UDF was invoked with the object name only: * 01400000
* - ARGV[0] = (input) pointer to a char[9], null- * 01410000
* terminated string having the name of * 01420000
* this program (DSN8DUTI) * 01430000
* - ARGV[1] = (input) pointer to a char[19], null- * 01440000
* terminated string having the object name * 01450000
* to be used as the starting point of the * 01460000
* alias resolution * 01470000
* - ARGV[2] = (output) pointer to a null-terminated * 01480000
* string to receive the result as follows: * 01490000
* - char[19] for the TABLE_NAME UDF * 01500000
* - char[9] for the TABLE_SCHEMA UDF * 01510000
* - char[17] for the TABLE_LOCATION UDF * 01520000
* - ARGV[3] = (input) pointer to a short integer * 01530000
* having the null indicator for the object * 01540000
* name * 01550000
* - ARGV[4] = (output) pointer to a short integer * 01560000
* having the null indicator for the result * 01570000
* - ARGV[5] = (output) pointer to a char[6], null- * 01580000
* terminated string to receive the * 01590000
* SQLSTATE * 01600000
* - ARGV[6] = (input) pointer to a char[138], null- * 01610000
* terminated string having the UDF family * 01620000
* name of the function * 01630000
* - ARGV[7] = (input) pointer to a char[129], * 01645990
* null-terminated * 01651980
* string having the UDF specific name of * 01660000
* the function * 01670000
* - ARGV[8] = (output) pointer to a char[70], * 01680000
* null-terminated string to receive any * 01690000
* diagnostic message issued by this * 01700000
* function * 01710000
* * 01720000
* If the UDF was invoked with the object name and the * 01730000
* object schema (but not the object location name): * 01740000
* - ARGV[0] = (input) pointer to a char[9], null- * 01750000
* terminated string having the name of * 01760000
* this program (DSN8DUTI) * 01770000
* - ARGV[1] = (input) pointer to a char[19], null- * 01780000
* terminated string having the object name * 01790000
* to be used in conjunction with the * 01800000
* object schema as the starting point of * 01810000
* the alias resolution * 01820000
* - ARGV[2] = (input) pointer to a char[9], null- * 01830000
* terminated string having the object * 01840000
* schema to be in used in conjunction with * 01850000
* the object name as the starting point of * 01860000
* the alias resolution * 01870000
* - ARGV[3] = (output) pointer to a null-terminated * 01880000
* string to receive the result as follows: * 01890000
* - char[19] for the TABLE_NAME UDF * 01900000
* - char[9] for the TABLE_SCHEMA UDF * 01910000
* - char[17] for the TABLE_LOCATION UDF * 01920000
* - ARGV[4] = (input) pointer to a short integer * 01930000
* having the null indicator for the object * 01940000
* name * 01950000
* - ARGV[5] = (input) pointer to a short integer * 01960000
* having the null indicator for the object * 01970000
* schema * 01980000
* - ARGV[6] = (output) pointer to a short integer * 01990000
* having the null indicator for the result * 02000000
* - ARGV[7] = (output) pointer to a char[6], null- * 02010000
* terminated string to receive the * 02020000
* SQLSTATE * 02030000
* - ARGV[8] = (input) pointer to a char[138], null- * 02040000
* terminated string having the UDF family * 02050000
* name of the function * 02060000
* - ARGV[9] = (input) pointer to a char[129], * 02070000
* null- terminated * 02080000
* string having the UDF specific name of * 02090000
* the function * 02100000
* - ARGV[10] = (output) pointer to a char[70], * 02110000
* null- terminated string to receive any * 02120000
* diagnostic message issued by this * 02130000
* function * 02140000
* * 02150000
* If the UDF was invoked with the object name and the * 02160000
* object schema and the object location name: * 02170000
* - ARGV[0] = (input) pointer to a char[9], null- * 02180000
* terminated string having the name of * 02190000
* this program (DSN8DUTI) * 02200000
* - ARGV[1] = (input) pointer to a char[19], null- * 02210000
* terminated string having the object name * 02220000
* to be used in conjunction with the * 02230000
* object schema and the object location * 02240000
* name as the starting point of the alias * 02250000
* resolution * 02260000
* - ARGV[2] = (input) pointer to a char[9], null- * 02270000
* terminated string having the object * 02280000
* schema to be in used in conjunction with * 02290000
* the object name and the object location * 02300000
* name as the starting point of the alias * 02310000
* resolution * 02320000
* - ARGV[3] = (input) pointer to a char[17], null- * 02330000
* terminated string having the object * 02340000
* location name to be used in conjunction * 02350000
* with the object name and the object * 02360000
* schema as the starting point of the * 02370000
* alias resolution * 02380000
* - ARGV[4] = (output) pointer to a null-terminated * 02390000
* string to receive the result as follows: * 02400000
* - char[19] for the TABLE_NAME UDF * 02410000
* - char[9] for the TABLE_SCHEMA UDF * 02420000
* - char[17] for the TABLE_LOCATION UDF * 02430000
* - ARGV[5] = (input) pointer to a short integer * 02440000
* having the null indicator for the object * 02450000
* name * 02460000
* - ARGV[6] = (input) pointer to a short integer * 02470000
* having the null indicator for the object * 02480000
* schema * 02490000
* - ARGV[7] = (input) pointer to a short integer * 02500000
* having the null indicator for the object * 02510000
* location name * 02520000
* - ARGV[8] = (output) pointer to a short integer * 02530000
* having the null indicator for the result * 02540000
* - ARGV[9] = (output) pointer to a char[6], null- * 02550000
* terminated string to receive the * 02560000
* SQLSTATE * 02570000
* - ARGV[10] = (input) pointer to a char[138], null- * 02580000
* terminated string having the UDF family * 02590000
* name of the function * 02600000
* - ARGV[11] = (input) pointer to a char[129], * 02610000
* null- terminated * 02620000
* string having the UDF specific name of * 02630000
* the function * 02640000
* - ARGV[12] = (output) pointer to a char[70], * 02650000
* null- terminated string to receive any * 02660000
* diagnostic message issued by this * 02670000
* function * 02680000
* * 02690000
* Normal Exit: Return Code: SQLSTATE = 00000 * 02700000
* - Message: none * 02710000
* * 02720000
* Error Exit: Return Code: SQLSTATE = 38601 * 02730000
* - Message: DSN8DUTI Error: Invocation by unexpected * 02740000
* UDF having specific name * 02750000
* <specific name> * 02760000
* Return Code: SQLSTATE = 38602 * 02770000
* - Message: DSN8DUTI Error: Unexpected SQLCODE, * 02780000
* <SQLCODE>, from SQL SELECT * 02790000
* * 02800000
* External References: * 02810000
* - Routines/Services: None * 02820000
* - Data areas : None * 02830000
* - Control blocks : None * 02840000
* * 02850000
* * 02860000
* Pseudocode: * 02870000
* DSN8DUTI: * 02880000
* - Walk down the argv list, locating the input and output parms * 02890000
* - If no object name passed, return null result * 02900000
* - If no object schema passed, assign default schema (current * 02910000
* SQLID) to object schema * 02920000
* - Concatenate wildcard ("%") to object location name * 02930000
* - SELECT TBNAME, TBCREATOR, and LOCATION from SYSIBM.SYSTABLES * 02940000
* where NAME is the object name, CREATOR is the object creator, * 02950000
* LOCATION is LIKE the object location name, and TYPE is "A" for * 02960000
* alias. * 02970000
* - if there's a result (SQLCODE = 0) then * 02980000
* - if the TABLE_NAME UDF is the invoker, assign the result * 02990000
* from TBNAME and return * 03000000
* - else if the TABLE_SCHEMA UDF is the invoker, assign the * 03010000
* result from TBCREATOR and return * 03020000
* - else if the TABLE_LOCATION UDF is the invoker, assign the * 03030000
* result from LOCATION and return * 03040000
* - else an unexpected UDF is the invoker so issue SQLSTATE * 03050000
* 38601 and a diagnstic message and return * 03060000
* - else if there's no result (SQLCODE = 100) then * 03070000
* - if the TABLE_NAME UDF is the invoker, assign the result * 03080000
* from the object name and return * 03090000
* - else if the TABLE_SCHEMA UDF is the invoker, assign the * 03100000
* result from the object schema and return * 03110000
* - else if the TABLE_LOCATION UDF is the invoker, remove the * 03120000
* trailing search wildcard ("%") from and assign the result * 03130000
* from LOCATION and return * 03140000
* - else an unexpected UDF is the invoker so issue SQLSTATE * 03150000
* 38601 and a diagnstic message and return * 03160000
* - else there's an unexpected SQLCODE so issue SQLSTATE 38602 * 03170000
* and a diagnostic message and return * 03180000
* End DSN8DUTI * 03190000
* * 03200000
* * 03210000
*********************************************************************/ 03220000
03230000
/********************** C library definitions ***********************/ 03270000
#include <stdio.h> 03280000
#include <stdlib.h> 03290000
#include <string.h> 03300000
03310000
/***************************** Equates ******************************/ 03320000
#define NULLCHAR '\0' /* Null character */ 03330000
03340000
#define MATCH 0 /* Comparison status: Equal */ 03350000
#define NOT_OK 0 /* Run status indicator: Error*/ 03360000
#define OK 1 /* Run status indicator: Good */ 03370000
03380000
03390000
/******************** DB2 SQL Communication Area ********************/ 03400000
EXEC SQL INCLUDE SQLCA; 03410000
03420000
03430000
/************************ DB2 Host Variables ************************/ 03440000
EXEC SQL BEGIN DECLARE SECTION; 03450000
char hvObjName[19]; /* host var for object name */ 03460000
short int *niObjName; /* indic var for hvObjName */ 03470000
char hvObjSchema[9]; /* host var for obj schema */ 03480000
short int *niObjSchema; /* indic var for hvObjSchema */ 03490000
char hvObjLocation[18]; /* host var for obj location */ 03500000
short int *niObjLocation; /* indic var for hvObjLocation*/ 03510000
char hvLOCATION[17]; /* host var for LOCATION col */ 03520000
char hvTBCREATOR[9]; /* host var for TBCREATOR col */ 03530000
char hvTBNAME[19]; /* host var for TBNAME column */ 03540000
EXEC SQL END DECLARE SECTION; 03550000
03560000
03570000
int main( int argc, char *argv[] ) 03580000
{ 03590000
/************************ local variables *************************/ 03600000
03610000
short int minus1 = -1; /* default null indic setting */ 03620000
03630000
char *result; /* result of this function */ 03640000
short int *niResult; /* indic var, result */ 03650000
char *sqlstate; /* SQLSTATE */ 03660000
char fnName[138]; /* function name */ 03675990
char specificName[129]; /* specific name of function */ 03681980
char *message; /* diagnostic message */ 03690000
03700000
short int status = OK; /* DSN8DUTI run status */ 03710000
03720000
03730000
/******************************************************************* 03740000
* Walk down the argv list, locating the input and output parms * 03750000
*******************************************************************/ 03760000
argc--; /* convert argc to base 0 index*/ 03763000
03766000
message = (char *)argv[argc--]; /* out: point to UDF diag msg */ 03770000
03780000
strcpy( specificName, /* in: save UDF specific name */ 03790000
argv[argc--]); 03800000
03810000
strcpy( fnName,argv[argc--] ); /* in: save UDF function name */ 03820000
03830000
sqlstate = (char *)argv[argc--]; /* out: point to UDF sqlstate */ 03840000
03850000
niResult /* out: point to null indicator*/ 03860000
= (short int *)argv[argc--]; /* variable for result */ 03870000
03880000
if( argc == 7 ) /* if 3 input parms passed */ 03890000
niObjLocation /* ..in: point to null indic. */ 03900000
= (short int *)argv[argc--]; /* var for object loc'n */ 03910000
else /* otherwise it wasn't passed */ 03920000
niObjLocation = &minus1; /* ..so define it as null */ 03930000
03940000
if( argc >= 5 ) /* if 2 or 3 input parms passed*/ 03950000
niObjSchema /* ..in: point to null indic. */ 03960000
= (short int *)argv[argc--]; /* var for object schema */ 03970000
else /* otherwise it wasn't passed */ 03980000
niObjSchema = &minus1; /* ..so define it as null */ 03990000
04000000
niObjName /* in: point to null indicator */ 04010000
= (short int *)argv[argc--]; /* var for object name */ 04020000
04030000
result = (char *)argv[argc--]; /* out: point to UDF result */ 04040000
04050000
if( argc == 3 ) /* if 3 input parms passed */ 04060000
strcpy( hvObjLocation, /* ..in: save object location */ 04070000
argv[argc--] ); /* name */ 04080000
else /* otherwise it wasn't passed */ 04090000
hvObjLocation[0] = NULLCHAR; /* ..so define it as null */ 04100000
04110000
if( argc >= 2 ) /* if 2 or 3 input parms passed*/ 04120000
strcpy( hvObjSchema, /* ..in: save object schema */ 04130000
argv[argc--] ); /* */ 04140000
else /* otherwise it wasn't passed */ 04150000
hvObjSchema[0] = NULLCHAR; /* ..so define it as null */ 04160000
04170000
strcpy( hvObjName,argv[argc] ); /* in: save object name */ 04180000
04190000
04200000
/******************************************************************* 04210000
* Initialize output parms * 04220000
*******************************************************************/ 04230000
message[0] = NULLCHAR; 04240000
strcpy( sqlstate,"00000" ); 04250000
*niResult = 0; 04260000
result[0] = NULLCHAR; 04270000
04280000
/******************************************************************* 04290000
* If no object name provided, return null result * 04300000
*******************************************************************/ 04310000
if( ( *niObjName != 0 ) || ( strlen( hvObjName ) == 0 ) ) 04320000
status = NOT_OK; 04330000
/******************************************************************* 04340000
* If no object schema provided, assign default schema * 04350000
*******************************************************************/ 04360000
if( ( *niObjSchema != 0 ) || ( strlen( hvObjSchema ) == 0 ) ) 04370000
EXEC SQL SET :hvObjSchema = CURRENT SQLID; 04380000
/******************************************************************* 04390000
* Concatenate "wildcard" to object location * 04400000
*******************************************************************/ 04410000
strcat( hvObjLocation,"%" ); 04420000
04430000
/******************************************************************* 04440000
* Look for alias with the object name (and schema (and location)) * 04450000
*******************************************************************/ 04460000
if( status == OK ) 04470000
{ 04480000
EXEC SQL SELECT TBNAME, 04490000
TBCREATOR, 04500000
LOCATION 04510000
INTO :hvTBNAME, 04520000
:hvTBCREATOR, 04530000
:hvLOCATION 04540000
FROM SYSIBM.SYSTABLES 04550000
WHERE NAME = :hvObjName 04560000
AND CREATOR = :hvObjSchema 04570000
AND LOCATION LIKE :hvObjLocation 04580000
AND TYPE = 'A'; 04590000
04600000
if( SQLCODE == 0 ) 04610000
/************************************************************* 04620000
* If such an alias was found ... * 04630000
*************************************************************/ 04640000
if( strncmp( specificName,"DSN8DUTIN",9 ) == 0 ) 04650000
/*********************************************************** 04660000
* TABLE_NAME UDF: return true name of table or view * 04670000
***********************************************************/ 04680000
strcpy( result,hvTBNAME ); 04690000
else if( strncmp( specificName,"DSN8DUTIS",9 ) == 0 ) 04700000
/*********************************************************** 04710000
* TABLE_SCHEMA UDF: return true schema of table or view * 04720000
***********************************************************/ 04730000
strcpy( result,hvTBCREATOR ); 04740000
else if( strncmp( specificName,"DSN8DUTIL",9 ) == 0 ) 04750000
/*********************************************************** 04760000
* TABLE_LOCATION UDF: return true loc'n of table or view * 04770000
***********************************************************/ 04780000
strcpy( result,hvLOCATION ); 04790000
else 04800000
/*********************************************************** 04810000
* Unknown UDF: signal error * 04820000
***********************************************************/ 04830000
{ 04840000
status = NOT_OK; 04850000
strcpy( sqlstate,"38601" ); 04860000
sprintf( message, 04870000
"DSN8DUTI Error: Invocation by unexpected UDF ", 04880000
"having specific name %s", 04890000
specificName ); 04900000
} 04910000
04920000
else if( SQLCODE == 100 ) 04930000
/************************************************************* 04940000
* If no such alias was found ... * 04950000
*************************************************************/ 04960000
if( strncmp( specificName,"DSN8DUTIN",9 ) == 0 ) 04970000
/*********************************************************** 04980000
* TABLE_NAME UDF: return starting point * 04990000
***********************************************************/ 05000000
strcpy( result,hvObjName ); 05010000
else if( strncmp( specificName,"DSN8DUTIS",9 ) == 0 ) 05020000
/*********************************************************** 05030000
* TABLE_SCHEMA UDF: return schema of starting point * 05040000
***********************************************************/ 05050000
strcpy( result,hvObjSchema ); 05060000
else if( strncmp( specificName,"DSN8DUTIL",9 ) == 0 ) 05070000
/*********************************************************** 05080000
* TABLE_LOCATION UDF: Remove trailing search wildcard byte * 05090000
* and return location of starting point * 05100000
***********************************************************/ 05110000
{ 05120000
hvObjLocation[strlen(hvObjLocation)-1] = NULLCHAR; 05130000
strcpy( result,hvObjLocation ); 05140000
} 05150000
else 05160000
/*********************************************************** 05170000
* Unknown UDF: signal error * 05180000
***********************************************************/ 05190000
{ 05200000
status = NOT_OK; 05210000
strcpy( sqlstate,"38601" ); 05220000
sprintf( message, 05230000
"DSN8DUTI Error: Invocation by unexpected UDF ", 05240000
"having specific name %s", 05250000
specificName ); 05260000
} 05270000
05280000
else 05290000
/************************************************************* 05300000
* If unexpected SQLCODE, issue message * 05310000
*************************************************************/ 05320000
{ 05330000
status = NOT_OK; 05340000
strcpy( sqlstate,"38602" ); 05350000
sprintf( message, 05360000
"DSN8DUTI Error: Unexpected SQLCODE, %d, " 05370000
"from SQL SELECT", 05380000
SQLCODE ); 05390000
} 05400000
} /* end if( status == OK ) */ 05410000
05420000
/******************************************************************* 05430000
* If null starting point or unexpected SQLCODE, return null result * 05440000
*******************************************************************/ 05450000
if( status == NOT_OK ) 05460000
{ 05470000
result[0] = NULLCHAR; 05480000
*niResult = -1; 05490000
} 05500000
else 05510000
{ 05520000
*niResult = 0; 05530000
strcpy( sqlstate,"00000" ); 05540000
} 05560000
05570000
} /* end DSN8DUTI */ 05620000