DSN8DTS1
Shows how to use the following stored procedures to create and initialize an index for the Db2 Text Search feature.
/*********************************************************************
* Module name = DSN8DTS1 (DB2 sample program) *
* *
* DESCRIPTIVE NAME = Create an index for DB2 Text Search *
* *
* Licensed Materials - Property of IBM *
* 5615-DB2 *
* (C) COPYRIGHT 1982, 2013 IBM Corp. All Rights Reserved. *
* *
* STATUS = Version 11 *
* *
* Function: Shows how to use the following stored procedures *
* to create and initialize an index for the DB2 *
* Text Search feature: *
* - SYSPROC.SYSTS_START *
* - SYSPROC.SYSTS_CREATE *
* - SYSPROC.SYSTS_UPDATE *
* *
* Notes: *
* Dependencies: Requires IBM C/C++ for z/OS *
* *
* Restrictions: *
* *
* Module type: C program *
* Processor: IBM C/C++ for z/OS *
* Module size: See linkedit output *
* Attributes: Re-entrant and re-usable *
* *
* Entry Point: DSN8DTS1 *
* Purpose: See Function *
* Linkage: Standard z/OS linkage *
* *
* *
* Parameters: DSN8DTS1 uses the C "main" argument convention of *
* argv (argument vector) and argc (argument count). *
* *
* - ARGV[0]: pointer to a char[9], *
* null-terminated string having the name of *
* this program (DSN8DTS1) *
* - ARGV[1]: pointer to a char[9], *
* null-terminated string having the name of *
* the schema for the index to be created *
* - ARGV[2]: pointer to a char[9], *
* null-terminated string having the name of *
* the index to be created *
* - ARGV[3]: pointer to a char[20], *
* null-terminated string having the name of *
* the table and column on which to create *
* the index *
* *
* Normal Exit: Return Code: 0000 *
* - Message: Indexes were created successfully *
* *
* Error Exit: Return Code: 0012 *
* - Message: DSN8DTS1 was invoked with <parameter-count>*
* parameters. Three parameters are required *
* - Message: <formatted SQL text from GET DIAGNOSTICS> *
* *
* *
* External References: *
* - Routines/Services: DSNTIAR: DB2 msg text formatter *
* - Data areas : None *
* - Control blocks : None *
* *
* Pseudocode: *
* DSN8DTS1: *
* - Get the input parameters *
* - if unsuccessful, call issueInvalidCallParmCountError to *
* issue a diagnostic message, then end with code 0012. *
* - Call SYSPROC.SYSTS_START to activate Text Search services *
* - if unsuccessful, call issueSqlError to issue a diagnostic *
* message, then end with code 0012. *
* - Call SYSPROC.SYSTS_CREATE to create a text search index on the *
* specified table and column *
* - if unsuccessful, call issueSqlError to issue a diagnostic *
* message, then end with code 0012. *
* - Call SYSPROC.SYSTS_UPDATE to initialize the specified index *
* - if unsuccessful, call issueSqlError to issue a diagnostic *
* message, then end with code 0012. *
* - Call SYSPROC.SYSTS_STOP to deactivate Text Search services *
* - if unsuccessful, call issueSqlError to issue a diagnostic *
* message, then end with code 0012. *
* -> This code is disabled deliberately *
* End DSN8DTS1 *
* *
* issueInvalidCallParmCountError *
* - report invalid parameter count *
* End issueSqlError *
* *
* issueSqlError: *
* - call GET DIAGNOSTICS to format the unexpected SQLCODE. *
* End issueSqlError *
* *
*********************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#define BLANK ' ' /* Buffer padding */
#define RETSEV 12 /* Severe error return code */
#define RETOK 0 /* No error return code */
#define NULLCHAR '\0' /* NULL character */
/*******************************************************************/
/* Declare global variables. */
/*******************************************************************/
long int rc; /* Return code */
EXEC SQL INCLUDE SQLCA;
void issueInvalidCallParmCountError /* Handler for parm count err */
( int argc /* - in: no. parms received */
);
void issueSqlError /* Handler for SQL error */
( char *locMsg /* - in: Call location */
);
int main (int argc, char *argv[])
{ EXEC SQL BEGIN DECLARE SECTION;
struct { short len; char s[9]; } hv_ischema;
struct { short len; char s[9]; } hv_iname;
struct { short len; char s[30]; } hv_tabname;
struct { short len; char s[2]; } hv_blank;
EXEC SQL END DECLARE SECTION;
rc = RETOK;
/*****************************************************************
* Verify that three arguments (plus arg[0]) were passed in *
*****************************************************************/
if( argc != 4 )
{ issueInvalidCallParmCountError( argc );
}
/*****************************************************************
* Walk down the argv list, locating the input parms *
*****************************************************************/
if( rc == RETOK )
{ argc--; /* convert argc to base 0 indx*/
strcpy( hv_tabname.s /* parm 3: Table name */
,argv[argc--]);
hv_tabname.len
= strlen(hv_tabname.s);
strcpy( hv_iname.s /* parm 2: Index name */
,argv[argc--]);
hv_iname.len
= strlen(hv_iname.s);
strcpy( hv_ischema.s /* parm 1: Index schema name */
,argv[argc--]);
hv_ischema.len
= strlen(hv_ischema.s);
strcpy(hv_blank.s, " "); /* initialize other storage */
hv_blank.len
= strlen(hv_blank.s);
}
/*****************************************************************
* Call SYSPROC.SYSTS_START to activate Text Search services *
*****************************************************************/
if( rc == RETOK )
{ EXEC SQL CALL SYSPROC.SYSTS_START();
if (SQLCODE != 0)
issueSqlError("DB2 error from SYSPROC.SYSTS_START call:");
}
/*****************************************************************
* Call SYSPROC.SYSTS_CREATE to create the specified index *
*****************************************************************/
if( rc == RETOK )
{ EXEC SQL CALL SYSPROC.SYSTS_CREATE(:hv_ischema,
:hv_iname,
:hv_tabname,
:hv_blank);
if (SQLCODE != 0)
issueSqlError("DB2 error from SYSPROC.SYSTS_CREATE call:");
}
/*****************************************************************
* Call SYSPROC.SYSTS_UPDATE to refresh the specified index *
*****************************************************************/
if( rc == RETOK )
{ EXEC SQL CALL SYSPROC.SYSTS_UPDATE(:hv_ischema,
:hv_iname,
:hv_blank);
if (SQLCODE != 0)
issueSqlError("DB2 error from SYSPROC.SYSTS_UPDATE call:");
}
/*****************************************************************
* Call SYSPROC.SYSTS_STOP to deactivate Text Search services *
*****************************************************************/
/*******************************************************************
** if( rc == RETOK ) **
** { EXEC SQL CALL SYSPROC.SYSTS_STOP(); **
** if (SQLCODE != 0) **
** issueSqlError("DB2 error from SYSPROC.SYSTS_STOP call:");**
** } **
*******************************************************************/
return rc;
} /* end of main */
void issueInvalidCallParmCountError /* Handler for parm count err */
( int argc /* - in: no. parms received */
)
/*******************************************************************
* Called when this program is invoked with an inappropriate number *
* of call parms. *
*******************************************************************/
{ printf( "ERROR: DSN8DTS1 was invoked with %i parameters\n"
,--argc );
printf( " but three parms (index schema, index "
"name, and table name) are required\n" );
printf( "-----> Processing halted\n" );
rc = RETSEV;
} /* end of issueInvalidCallParmCountError */
#pragma linkage(dsntiar, OS)
void issueSqlError /* Handler for SQL error */
( char *locMsg /* - in: Call location */
)
/*******************************************************************
* Called when an unexpected SQLCODE is returned from a DB2 call *
*******************************************************************/
{ EXEC SQL BEGIN DECLARE SECTION;
long j;
long numErr;
long sCode;
char sMessageId[10] = {0};
char sMessage[2048] = {0};
char sState[6] = {0};
char sModule[9] = {0};
long sErrD1;
long sErrD2;
long sErrD3;
long sErrD4;
long sErrD5;
long sErrD6;
EXEC SQL END DECLARE SECTION;
struct error_struct { /* DSNTIAR message structure */
short int error_len;
char error_text[10][80];
} error_message = {10 * 80};
extern short int dsntiar( struct sqlca *sqlca,
struct error_struct *msg,
int *len );
short int DSNTIARrc; /* DSNTIAR Return code */
static int lrecl = 80; /* Width of message lines */
/*****************************************************************
* print the locator message *
*****************************************************************/
printf( "ERROR: %-80s\n", locMsg );
printf( "-----> Processing halted\n" );
/*****************************************************************
* print messages for the SQLCODE(s) *
*****************************************************************/
EXEC SQL GET DIAGNOSTICS :numErr = NUMBER; /* GET NUM OF ERRORS */
for( j=1; j==numErr; j++ ) /* Loop thru errors */
{ EXEC SQL GET DIAGNOSTICS CONDITION :j
:sCode = DB2_RETURNED_SQLCODE,
:sMessageId = DB2_MESSAGE_ID,
:sMessage = MESSAGE_TEXT,
:sState = RETURNED_SQLSTATE,
:sModule = DB2_MODULE_DETECTING_ERROR,
:sErrD1 = DB2_SQLERRD1,
:sErrD2 = DB2_SQLERRD2,
:sErrD3 = DB2_SQLERRD3,
:sErrD4 = DB2_SQLERRD4,
:sErrD5 = DB2_SQLERRD5,
:sErrD6 = DB2_SQLERRD6;
printf( " SQLCODE = %i, %s\n", sCode, sMessage );
printf( " SQLSTATE RETURN CODE = %s\n", sState );
printf( " SQL PROCEDURE DETECTING ERROR = %s\n",sModule );
printf( " SQL DIAGNOSTIC INFO = %i %i %i %i %i %i\n",
sErrD1,sErrD2,sErrD3,sErrD4,sErrD5,sErrD6 );
printf( " SQL DIAGNOSTIC INFO = "
"X'%X' X'%X' X'%X' X'%X' X'%X' X'%X'\n",
sErrD1,sErrD2,sErrD3,sErrD4,sErrD5,sErrD6 );
}
if( SQLCODE != 0 ) /* Check the SQLCODE from GET DIAGNOSTICS*/
{ /*************************************************************
* GET DIAGNOSTICS failed: Use DSNTIAR to report GET DIAG err *
*************************************************************/
printf( "-----> GET DIAGNOSTICS request failed with these "
"symptoms:\n" );
/*************************************************************
* format and print the SQL message for GET DIAG failure *
*************************************************************/
DSNTIARrc = dsntiar( &sqlca, &error_message, &lrecl );
if( DSNTIARrc == 0 )
for( j = 0; j <= 10; j++ )
printf( " %.80s\n", error_message.error_text[j] );
else
{ printf( " *** ERROR: DSNTIAR could not format "
"the message\n" );
printf( " *** SQLCODE is %d\n",SQLCODE );
printf( " *** SQLERRM is \n" );
for( j=0; j<sqlca.sqlerrml; j++ )
printf( "%c", sqlca.sqlerrmc[j] );
printf( "\n" );
}
}
/*****************************************************************
* set severe error code *
*****************************************************************/
rc = RETSEV;
} /* end of issueSqlError */