DB2 Data Links Manager Einstieg

quiesce.c

------------------------- 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 ]