DB2 Data Links Manager Einstieg
------------------------- start of 'quiesce.c' script ------------------------
/**********************************************************************
*
* OCO SOURCE MATERIALS
*
* COPYRIGHT: P#2 P#1
* (C) COPYRIGHT IBM CORPORATION Y1, Y2
*
* The source code for this program is not published or otherwise divested of
* its trade secrets, irrespective of what has been deposited with the U.S.
* Copyright Office.
*
* Source File Name = quiesce.c (00003558.ide, db2_install, db2z6_v7)
*
* Descriptive Name = Quiesce or Reset tables.
*
* Function: It quiesces ( OR resets ) the tables ( with datalinks column ) of
* the databases which are registered with DLFM_DB
*
* This program expects the databases registered with DLFM_DB are
* catalogued. It also expects that db2 is started.
*
* Dependencies:
*
* Restrictions:
*
***********************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <sqlcli1.h>
#include <sqlutil.h>
#include <sqlca.h>
#define MAX_UID_LENGTH 20
#define MAX_PWD_LENGTH 20
#define MAXCOLS 255
struct sqlca sqlca;
struct SQLB_TBSPQRY_DATA *sqlb;
#ifndef max
#define max(a,b) (a > b ? a : b)
#endif
#define CHECK_HANDLE( htype, hndl, RC ) if ( RC Ü= SQL_SUCCESS ) \
{ check_error( htype, hndl, RC, __LINE__, __FILE__ ) ; }
SQLRETURN check_error( SQLSMALLINT, SQLHANDLE, SQLRETURN, int, char * ) ;
SQLRETURN DBconnect( SQLHANDLE, SQLHANDLE * ) ;
SQLRETURN print_error( SQLSMALLINT, SQLHANDLE, SQLRETURN, int, char * ) ;
SQLRETURN prompted_connect( SQLHANDLE, SQLHANDLE * ) ;
SQLRETURN terminate( SQLHANDLE, SQLRETURN ) ;
SQLCHAR server[SQL_MAX_DSN_LENGTH + 1] ;
SQLCHAR uid[MAX_UID_LENGTH + 1] ;
SQLCHAR pwd[MAX_PWD_LENGTH + 1] ;
/* check_error - calls print_error(), checks severity of return code */
SQLRETURN check_error( SQLSMALLINT htype, /* A handle type identifier */
SQLHANDLE hndl, /* A handle */
SQLRETURN frc, /* Return code to be included with error msg */
int line, /* Used for output message, indicate where */
char * file /* the error was reported from */
) {
print_error( htype, hndl, frc, line, file ) ;
switch ( frc ) {
case SQL_SUCCESS:
break ;
case SQL_INVALID_HANDLE:
printf( "\n>------ ERROR Invalid Handle --------------------------\n");
case SQL_ERROR:
printf( "\n>--- FATAL ERROR, Attempting to rollback transaction --\n");
if ( SQLEndTran( htype, hndl, SQL_ROLLBACK ) Ü= SQL_SUCCESS )
printf( ">Rollback Failed, Exiting application\n" ) ;
else
printf( ">Rollback Successful, Exiting application\n" ) ;
return( terminate( hndl, frc ) ) ;
case SQL_SUCCESS_WITH_INFO:
printf( "\n> ----- Warning Message,
application continuing ------- \n");
break ;
case SQL_NO_DATA_FOUND:
printf( "\n> ----- No Data Found, application continuing --------- \n");
break ;
default:
printf( "\n> ----------- Invalid Return Code --------------------- \n");
printf( "> --------- Attempting to rollback transaction ---------- \n");
if ( SQLEndTran( htype, hndl, SQL_ROLLBACK ) Ü= SQL_SUCCESS )
printf( ">Rollback Failed, Exiting application\n" ) ;
else
printf( ">Rollback Successful, Exiting application\n" ) ;
return( terminate( hndl, frc ) ) ;
}
return ( frc ) ;
}
/* connect without prompt */
SQLRETURN DBconnect( SQLHANDLE henv,
SQLHANDLE * hdbc
) {
/* allocate a connection handle */
if ( SQLAllocHandle( SQL_HANDLE_DBC,
henv,
hdbc
) Ü= SQL_SUCCESS ) {
printf( ">---ERROR while allocating a connection handle-----\n" ) ;
return( SQL_ERROR ) ;
}
/* Set AUTOCOMMIT OFF */
if ( SQLSetConnectAttr( * hdbc,
SQL_ATTR_AUTOCOMMIT,
( void * ) SQL_AUTOCOMMIT_OFF, SQL_NTS
) Ü= SQL_SUCCESS ) {
printf( ">---ERROR while setting AUTOCOMMIT OFF ------------\n" ) ;
return( SQL_ERROR ) ;
}
if ( SQLConnect( * hdbc,
server, SQL_NTS,
uid, SQL_NTS,
pwd, SQL_NTS
) Ü= SQL_SUCCESS ) {
printf( ">--- Error while connecting to database: %s -------\n",
server
) ;
SQLDisconnect( * hdbc ) ;
SQLFreeHandle( SQL_HANDLE_DBC, * hdbc ) ;
return( SQL_ERROR ) ;
}
else /* Print Connection Information */
printf( "\nConnected to %s\n", server ) ;
return( SQL_SUCCESS ) ;
}
/*--> SQLL1X32.SCRIPT */
/* print_error - calls SQLGetDiagRec(), displays SQLSTATE and message **
** - called by check_error */
SQLRETURN print_error( SQLSMALLINT htype, /* A handle type identifier */
SQLHANDLE hndl, /* A handle */
SQLRETURN frc, /* Return code to be included with error msg */
int line, /* Used for output message, indicate where */
char * file /* the error was reported from */
) {
SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1] ;
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1] ;
SQLINTEGER sqlcode ;
SQLSMALLINT length, i ;
printf( ">--- ERROR -- RC = %d Reported from %s, line %d ------------\n",
frc,
file,
line
) ;
i = 1 ;
while ( SQLGetDiagRec( htype,
hndl,
i,
sqlstate,
&sqlcode,
buffer,
SQL_MAX_MESSAGE_LENGTH + 1,
&length
) == SQL_SUCCESS ) {
printf( " SQLSTATE: %s\n", sqlstate ) ;
printf( "Native Error Code: %ld\n", sqlcode ) ;
printf( "%s \n", buffer ) ;
i++ ;
}
printf( ">--------------------------------------------------\n" ) ;
return( SQL_ERROR ) ;
}
/*<-- */
/* prompted_connect - prompt for connect options and connect */
SQLRETURN prompted_connect( SQLHANDLE henv,
SQLHANDLE * hdbc
) {
/* allocate a connection handle */
if ( SQLAllocHandle( SQL_HANDLE_DBC,
henv,
hdbc
) Ü= SQL_SUCCESS ) {
printf( ">---ERROR while allocating a connection handle-----\n" ) ;
return( SQL_ERROR ) ;
}
/* Set AUTOCOMMIT OFF */
if ( SQLSetConnectAttr( * hdbc,
SQL_ATTR_AUTOCOMMIT,
( void * ) SQL_AUTOCOMMIT_OFF, SQL_NTS
) Ü= SQL_SUCCESS ) {
printf( ">---ERROR while setting AUTOCOMMIT OFF ------------\n" ) ;
return( SQL_ERROR ) ;
}
if ( SQLConnect( * hdbc,
server, SQL_NTS,
uid, SQL_NTS,
pwd, SQL_NTS
) Ü= SQL_SUCCESS ) {
printf( ">--- ERROR while connecting to %s -------------\n",
server
) ;
SQLDisconnect( * hdbc ) ;
SQLFreeHandle( SQL_HANDLE_DBC, * hdbc ) ;
return( SQL_ERROR ) ;
}
else /* Print Connection Information */
printf( "\nConnected to %s\n", server ) ;
return( SQL_SUCCESS ) ;
}
/* terminate and free environment handle */
SQLRETURN terminate( SQLHANDLE henv,
SQLRETURN rc
) {
SQLRETURN lrc ;
printf( ">Terminating ....\n" ) ;
print_error( SQL_HANDLE_ENV,
henv,
rc,
__LINE__,
__FILE__
) ;
/* Free environment handle */
if ( ( lrc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ) Ü= SQL_SUCCESS )
print_error( SQL_HANDLE_ENV,
henv,
lrc,
__LINE__,
__FILE__
) ;
return( rc ) ;
}
void show_progress()
{
int i;
for(i=0;i<3;i++)
{
printf("...");
/* sleep(1);*/
}
printf("... DONE.\n");
}
void wrong_input(char *str)
{
printf("\n\n\t**************************************************************\n");
printf("\t* usage: %s -q <DB-NAME> ( to Quiesce tables ..) *\n",str);
printf("\t* OR *\n");
printf("\t* usage: %s -r <DB-NAME> ( to reset Quiesced tables ..)*\n",str);
printf("\t**************************************************************\n\n\n");
exit(0);
}
extern SQLCHAR server[SQL_MAX_DSN_LENGTH + 1] ;
extern SQLCHAR uid[MAX_UID_LENGTH + 1] ;
extern SQLCHAR pwd[MAX_PWD_LENGTH + 1] ;
#define MAX_STMT_LEN 500
int reset=-1;
/*******************************************************************
** main
*******************************************************************/
int main( int argc, char * argv[] ) {
SQLHANDLE henv,hdbc[3], hstmt,hstmt1,hstmt2 ;
SQLRETURN rc ;
SQLCHAR * sqlstmt = ( SQLCHAR * ) "SELECT dbname,dbinst,password from dfm_dbid" ;
/* for the primary db */
SQLCHAR * stmt = ( SQLCHAR * ) "SELECT COLS.TBCREATOR, COLS.TBNAME
FROM SYSIBM.SYSCOLUMNS COLS, "
" SYSIBM.SYSCOLPROPERTIES PROPS WHERE COLS.TBCREATOR =
PROPS.TABSCHEMA AND "
" COLS.TBNAME = PROPS.TABNAME AND COLS.TYPENAME=
'DATALINK' AND SUBSTR(PROPS.DL_FEATURES, 2, 1) "
" = 'F' GROUP BY COLS.TBCREATOR, COLS.TBNAME";/*test
for the secondary db's*/
SQLCHAR * stmt2 = ( SQLCHAR * ) "SELECT count(*) from dfm_xnstate
where xn_state=3" ;/* for the primary db */
SQLCHAR v_dbname[20] ;
SQLINTEGER v_xnstate ;
SQLCHAR v_usernm[20] ;
SQLCHAR v_passwd[20] ;
SQLINTEGER nullind;
SQLVARCHAR v_tbname[128];
SQLCHAR v_tbcreator[20];
SQLINTEGER rowcount;
int i,count;
char state[6],v_tb[100];
int flag=0;
int xxx,tong=0;
if( (argc Ü= 2 && argcÜ=3) || argv[1][0]
Ü='-' || strlen(argv[1]) Ü=2) wrong_input(argv[0]);
/*** NOTE : If argc==2 then DB-NAME the program would ask user to enter
DB-Name else it would take the second argument to this program ( argv[2] )
as DB-NAME ***/
if(argv[1][1]=='q' || argv[1][1]=='Q')
{
reset=0;
}
else
{
if(argv[1][1]!='r' || argv[1][1]!='R')
{
reset=1;
}
else
{
wrong_input(argv[0]);
}
if(reset==-1) wrong_input(argv[0]);
}
SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
/*
Before allocating any connection handles, set Environment wide
Connect Options
Set to Connect Type 2, Syncpoint 1
*/
if ( SQLSetEnvAttr( henv,
SQL_CONNECTTYPE,
( SQLPOINTER ) SQL_COORDINATED_TRANS,
0
) != SQL_SUCCESS ) {
printf( ">---ERROR while setting Connect Type 2 -------------\n" ) ;
return( SQL_ERROR ) ;
}
/*<-- */
/*--> */
if ( SQLSetEnvAttr( henv,
SQL_SYNC_POINT,
( SQLPOINTER ) SQL_ONEPHASE,
0
) != SQL_SUCCESS ) {
printf( ">---ERROR while setting Syncpoint One Phase -------------\n" ) ;
return( SQL_ERROR ) ;
}
if(argc==3)
{
strcpy(server,argv[2]);
}
else
{
printf( ">Enter database Name:\n" ) ;
gets( ( char * ) server ) ;
}
/*prompted_connect(henv,&hdbc[0]);*/
/* allocate an environment handle */
rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
/* allocate a connect handle, and connect to the primary database*/
rc = DBconnect( henv, &hdbc[0] ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
flag=1;
if(reset!=1)
{
printf("\nWaiting for XNs to get over ...");
while(flag) /* Outer While */
{
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc[0], &hstmt2 ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc[0], rc ) ;
rc = SQLExecDirect( hstmt2, stmt2, SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol( hstmt2, 1, SQL_C_LONG, &v_xnstate, 0, &nullind) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt2, rc ) ;
while ( ( rc = SQLFetch( hstmt2 ) ) == SQL_SUCCESS )
{
/*printf( "\nCount of XNs Pending : %d \n",v_xnstate) ;*/
if (v_xnstate > 0)
{
fflush(stdout);
printf(".");
sleep(1);
break;
}
else flag=0;
} /* Inner While */
/* Deallocation */
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt2 ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt2, rc ) ;
} /* Outer While */
} /* IF */
if(!reset) printf("XNs OVER !!\n");
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc[0], &hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc[0], rc ) ;
rc = SQLExecDirect( hstmt, sqlstmt, SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol( hstmt, 1, SQL_C_CHAR, v_dbname, sizeof(v_dbname), NULL) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol( hstmt, 2, SQL_C_CHAR, v_usernm, sizeof(v_usernm), NULL) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
v_passwd[0]='\0';
rc = SQLBindCol( hstmt, 3, SQL_C_CHAR, v_passwd, sizeof(v_passwd), NULL) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Counter for number of rows fetched from the primary db*/
count=1;
for (i=1;i<=count;i++) /* For the FOR LOOP */
{
while ( ( rc = SQLFetch( hstmt ) ) == SQL_SUCCESS )
{
printf( "\nDatabase Name : %s \n",v_dbname) ;
count=count+1;
/* Depending on the no. of rows fetched from the primary db connect to the sec db's */
if ( SQLAllocHandle( SQL_HANDLE_DBC,henv,&hdbc[i]) != SQL_SUCCESS )
{
printf(">---ERROR while allocating a connection handle-----\n");
return( SQL_ERROR ) ;
}
/* Set AUTOCOMMIT ON */
if ( SQLSetConnectAttr( * hdbc,SQL_ATTR_AUTOCOMMIT,( void * ) SQL_AUTOCOMMIT_ON, SQL_NTS) != SQL_SUCCESS )
{
printf(">---ERROR while setting AUTOCOMMIT OFF ------------\n");
return( SQL_ERROR ) ;
}
rc = SQLConnect(hdbc[i],v_dbname,SQL_NTS,((v_passwd[0]=='\0') ? NULL : v_usernm),SQL_NTS,v_passwd,SQL_NTS);
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
/* tRYING OUT FOR SELECTION FROM THESE DB'S*/
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc[i], &hstmt1 ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc[i], rc ) ;
rc = SQLExecDirect( hstmt1, stmt, 276 ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt1, rc ) ;
rc = SQLBindCol( hstmt1, 1, SQL_C_CHAR, v_tbcreator, sizeof(v_tbcreator), NULL) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt1, rc ) ;
rc = SQLBindCol( hstmt1, 2, SQL_C_CHAR, v_tbname, sizeof(v_tbname), NULL) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt1, rc ) ;
while ( ( rc = SQLFetch( hstmt1 ) ) == SQL_SUCCESS )
{
v_tb[0]= '\0';
strcat(v_tb,v_tbcreator);
strcat(v_tb,".");
strcat(v_tb,v_tbname);
printf("\tTABLE : %s ",v_tb);
sqluvqdp (v_tb,(reset==1) ? 9 : 2, NULL, &sqlca);
/** 9 -> to RESET 2 -> to Quiesce ( exclusive) */
if (sqlca.sqlcode==0)
{
if (reset==1)
{
/* printf("The quiesced tablespace successfully reset.\n"); */
show_progress();
}
else
{
/* printf("The tablespace successfully quiesced\n");*/
show_progress();
}
}
else if (sqlca.sqlcode== -3805 ||sqlca.sqlcode==01004)
{
if(reset==1)
{
/* printf("The quiesced tablespace could not be reset.\n");*/
show_progress();
}
else
{
/* printf("The tablespace has already been quiesced\n");*/
show_progress();
}
}
else
{
if(reset==1)
{
printf("The quiesced tablespace could not be reset.\n");
}
else
{
printf("The tablespace could not be quiesced. \n");
}
printf("\t\tSQLCODE = %ld\n", sqlca.sqlcode);
strncpy(state, sqlca.sqlstate, 5);
state[5] = '\0';
printf("\t\tSQLSTATE = %s\n", state);
}
}
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt1 ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt1, rc ) ;
rc = SQLDisconnect( hdbc[i] );
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc[i], rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc[i] ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc[i], rc ) ;
}
}
printf("The NO. of DATABASES is %d \n",count-1);
if ( rc != SQL_NO_DATA_FOUND )
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Commit the changes. */
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc[0], SQL_COMMIT ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc[0], rc ) ;
/* Disconnect and free up CLI resources. */
rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* ******************************************************/
printf( "\n>Disconnecting .....\n" ) ;
rc = SQLDisconnect( hdbc[0] );
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc[0], rc ) ;
rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc[0] ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc[0], rc ) ;
/**********************************************************/
rc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
return( SQL_SUCCESS ) ;
} /* end main */
------------------------- end of 'quiesce.c' script ------------------------
[ Seitenanfang | Vorherige Seite | Nächste Seite | Inhaltsverzeichnis | Index ]