Wonderful tonight: ODBC Example (free

Free Space Check

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#include <time.h>
#include <signal.h>

#include <math.h>
#include <sys/time.h>

/* include SUNDB ODBC header */
#include <sundb.h>

/* User-specific Definitions */
#define SQL_MAX_MESSAGE_LENGTH 512

#define GL_TRY( stmt, aExpression ) \
do \
{ \
if( !(SQL_SUCCEEDED( aExpression ) ) ) \
{ \
printf("%s:%d error: ", __FILE__, __LINE__ ); \
printError(SQL_HANDLE_STMT, stmt); \
goto GLIESE_FINISH_LABEL; \
} \
} while( 0 )

#define GLIESE_SQL_TRY( aExpression ) \
do \
{ \
if( !(SQL_SUCCEEDED( aExpression ) ) ) \
{ \
printf("%s:%d: error : ", __FILE__, __LINE__ ); \
goto GLIESE_FINISH_LABEL; \
} \
} while( 0 )

#define GLIESE_SUCCESS \
goto GLIESE_SUCCESS_FINISH; \
GLIESE_SUCCESS_FINISH:

#define GLIESE_FINISH \
goto GLIESE_FINISH_LABEL; \
GLIESE_FINISH_LABEL:

/* Print Error to console */
void printError(SQLSMALLINT aHandleType, SQLHANDLE aHandle)
{
SQLSMALLINT i = 1;
SQLRETURN rc;
SQLCHAR sSQLState[6];
SQLINTEGER sNativeError;
SQLSMALLINT sTextLength;
SQLCHAR sMessageText[SQL_MAX_MESSAGE_LENGTH];

/* SQLGetDiagRec returns the current values that contains error, warning */
while( 1 )
{
rc = SQLGetDiagRec( aHandleType,
aHandle,
i,
sSQLState,
(SQLINTEGER*)&sNativeError,
sMessageText,
SQL_MAX_MESSAGE_LENGTH,
&sTextLength );
if( rc == SQL_NO_DATA ) {
break;
}

printf("[%2d]SQL STATE : %s\n", i, sSQLState );
printf("[%2d]NATIVE ERROR : %d\n", i, sNativeError );
printf("[%2d]MESSAGE : %s\n", i, sMessageText );
i++;
}
}

int timeval_subtract(struct timeval *result, struct timeval *t2, struct timeval *t1) {
long int diff = (t2->tv_usec + 1000000 * t2->tv_sec)
- (t1->tv_usec + 1000000 * t1->tv_sec);
result->tv_sec = diff / 1000000;
result->tv_usec = diff % 1000000;

return (diff < 0);
}

void timeval_print(struct timeval *tv) {
char buffer[1024] = {0x00,};
time_t curtime;

printf("%ld.%06ld", tv->tv_sec, tv->tv_usec);
curtime = tv->tv_sec;
strftime(buffer, 1024, "%m-%d-%Y %T", localtime(&curtime));
printf(" = %s.%06ld\n", buffer, tv->tv_usec);
}

int getTimestamp(char* p) {
char buffer[32] = {0x00,};
struct timeval tv;
gettimeofday(&tv, NULL);

time_t curtime;
curtime = (&tv)->tv_sec;
strftime(buffer, 32, "%Y%m%d%H%M%S", localtime(&curtime) );
return sprintf(p, "%s%03ld", buffer, ((&tv)->tv_usec/1000));
}

int getDateTime(char* p) {
struct timeval tv;
gettimeofday(&tv, NULL);

time_t curtime;
curtime = (&tv)->tv_sec;
return strftime(p, 32, "%Y%m%d%H%M%S", localtime(&curtime) );
}

int getNowDate(char* p) {
struct timeval tv;
gettimeofday(&tv, NULL);

time_t curtime;
curtime = (&tv)->tv_sec;
// strftime(p, 32, "%Y%m%d%H%M%S%03ld", localtime(&curtime), tv->tv_usec );
return strftime(p, 32, "%Y%m%d", localtime(&curtime) );
}

int getNowTime(char* p) {
char buffer[32] = {0x00,};
struct timeval tv;
gettimeofday(&tv, NULL);

time_t curtime;
curtime = (&tv)->tv_sec;
strftime(buffer, 32, "%H%M%S", localtime(&curtime) );
return sprintf(p, "%s%03ld", buffer, ((&tv)->tv_usec/1000));
}

// 각 파트별 백분위수를 위한 기간 배열
typedef struct {
double item;
double ptile;
} _st_ptile_items;

int callback_qsort_asc_item(const void* ptr1, const void* ptr2) {
return ((_st_ptile_items*)ptr1)->item > ((_st_ptile_items*)ptr2)->item;
}
int callback_qsort_desc_item(const void* ptr1, const void* ptr2) {
return ((_st_ptile_items*)ptr1)->item < ((_st_ptile_items*)ptr2)->item;
}

#define SQL_BUFF_SIZE 2048 * 4

/* ODBC variables */
SQLHENV gEnv = NULL;
SQLHDBC gDbc = NULL;
SQLHSTMT gStmt01 = NULL;
SQLHSTMT gStmt02 = NULL;
SQLHSTMT gStmt03 = NULL;
SQLHSTMT gStmt04 = NULL;
SQLHSTMT gStmt05 = NULL;
SQLHSTMT gStmt06 = NULL;
SQLHSTMT gStmt07 = NULL;
SQLHSTMT gStmt08 = NULL;
SQLHSTMT gStmt09 = NULL;
SQLHSTMT gStmt10 = NULL;
SQLHSTMT gStmt11 = NULL;
SQLHSTMT gStmt12 = NULL;
SQLHSTMT gStmt13 = NULL;
SQLHSTMT gStmt14 = NULL;
SQLHSTMT gStmt15 = NULL;

typedef struct _x$segment {
/*
* table column
*/
SQLBIGINT allocPageCount ;

/*
* table column indicator
*/
SQLLEN indallocPageCount ;
} x$segment_t;

typedef struct _tablespace_volume {
/*
* table column
*/
SQLCHAR name [128 + 1]; // tablespace name
SQLCHAR id [ 8 + 1]; // tablespace id
SQLCHAR tot [128 + 1]; // tablespace alloc total

/*
* table column indicator
*/
SQLLEN indname ;
SQLLEN indid ;
SQLLEN indtot ;
} tablespace_volume_t;

typedef struct {
/*
* table column
*/
SQLCHAR name [128 + 1]; // tablespace name
SQLCHAR tbsId [ 8 + 1]; // tablespace name
SQLCHAR tot [128 + 1]; // tablespace name
SQLCHAR used [ 25 + 1]; // used space
SQLCHAR free [ 25 + 1]; // free space

/*
* table column indicator
*/
SQLLEN indname ;
SQLLEN indtbsId ;
SQLLEN indtot ;
SQLLEN indused ;
SQLLEN indfree ;
} space_used_t;

typedef struct _d$tablespace_extent {
/*
* table column
*/
SQLCHAR dumy [ 1 + 1];

/*
* table column indicator
*/
SQLLEN inddumy ;
} d$tablespace_extent_t;

typedef struct _d$memory_segment_bitmap {
/*
* table column
*/
SQLCHAR id [ 32 + 1]; // "TABLE", "INDEX"
SQLCHAR name [128 + 1]; // TABLE_NAME or INDEX_NAME CHARACTER VARYING(128)
SQLCHAR schema [128 + 1]; // SCHEMA NAME CHARACTER VARYING(128)

/*
* table column indicator
*/
SQLLEN indid ; // TABLE or INDEX
SQLLEN indname ; // TABLE_NAME or INDEX_NAME CHARACTER VARYING(128)
SQLLEN indschema ; // SCHEMA NAME CHARACTER VARYING(128)
} d$memory_segment_bitmap_t;

typedef struct _object {
/*
* table column
*/
SQLBIGINT physicalId ; // PHYSICAL_ID BIGINT
SQLCHAR id [128 + 1]; // ID
SQLCHAR name [128 + 1]; // TABLE_NAME or INDEX_NAME CHARACTER VARYING(128)
SQLCHAR schema [128 + 1]; // SCHEMA NAME CHARACTER VARYING(128)
SQLCHAR tbsId [ 8 + 1]; // Tablespace ID

/*
* table column indicator
*/
SQLLEN indphysicalId ; // PHYSICAL_ID BIGINT
SQLLEN indid ; // ID
SQLLEN indname ; // TABLE_NAME or INDEX_NAME CHARACTER VARYING(128)
SQLLEN indschema ; // SCHEMA NAME CHARACTER VARYING(128)
SQLLEN indtbsId ; // tablespace id
} object_t;

typedef struct _x$session {
/*
* table column
*/
SQLBIGINT id ; // ID INTEGER 4
SQLCHAR username [128 + 1]; // USERNAME CHARACTER VARYING 128
SQLCHAR status [ 16 + 1]; // STATUS CHARACTER VARYING 10
SQLCHAR server [ 16 + 1]; // SERVER CHARACTER VARYING 10

/*
* table column indicator
*/
SQLLEN indid ; // ID INTEGER 4
SQLLEN indusername ; // USERNAME CHARACTER VARYING 128
SQLLEN indstatus ; // STATUS CHARACTER VARYING 10
SQLLEN indserver ; // SERVER CHARACTER VARYING 10
} x$session_t;

typedef struct _tbsv_sv_gm_dbf {
/*
* table column
*/
SQLBIGINT dataVol ; // DATA_VOL NUMERIC(15) , -- DATA영역SPACE
SQLBIGINT freeVol ; // FREE_VOL NUMERIC(15) , -- FREE영역SPACE
SQLBIGINT undoVol ; // UNDO_VOL NUMERIC(15) , -- UNDO영역SPACE
SQLCHAR creatDdtm [ 14 + 1]; // CREAT_DDTM VARCHAR(14) NOT NULL, -- 생성일시
SQLCHAR creatrId [ 10 + 1]; // CREATR_ID VARCHAR(10) NOT NULL, -- 생성자ID
SQLCHAR adjDdtm [ 14 + 1]; // ADJ_DDTM VARCHAR(14) NOT NULL, -- 수정일시
SQLCHAR adjprnId [ 10 + 1]; // ADJPRN_ID VARCHAR(10) NOT NULL, -- 수정자ID

/*
* table column indicator
*/
SQLLEN inddataVol ; // DATA_VOL NUMERIC(15) , -- DATA영역SPACE
SQLLEN indfreeVol ; // FREE_VOL NUMERIC(15) , -- FREE영역SPACE
SQLLEN indundoVol ; // UNDO_VOL NUMERIC(15) , -- UNDO영역SPACE
SQLLEN indcreatDdtm ; // CREAT_DDTM VARCHAR(14) NOT NULL, -- 생성일시
SQLLEN indcreatrId ; // CREATR_ID VARCHAR(10) NOT NULL, -- 생성자ID
SQLLEN indadjDdtm ; // ADJ_DDTM VARCHAR(14) NOT NULL, -- 수정일시
SQLLEN indadjprnId ; // ADJPRN_ID VARCHAR(10) NOT NULL, -- 수정자ID

} tbsv_sv_gm_dbf_t;

typedef struct _tbsv_sv_gm_data {
/*
* table column
*/
SQLCHAR tableName [128 + 1]; // TABLE_NAME VARCHAR(128) NOT NULL, -- 테이블명
SQLCHAR indexName [128 + 1]; // INDEX_NAME VARCHAR(128) NOT NULL, -- 인덱스명
SQLBIGINT occupyVol ; // OCCUPY_VOL NUMERIC(15) , -- 점유영역SPACE
SQLCHAR creatDdtm [ 14 + 1]; // CREAT_DDTM VARCHAR(14) NOT NULL, -- 생성일시
SQLCHAR creatrId [ 10 + 1]; // CREATR_ID VARCHAR(10) NOT NULL, -- 생성자ID
SQLCHAR adjDdtm [ 14 + 1]; // ADJ_DDTM VARCHAR(14) NOT NULL, -- 수정일시
SQLCHAR adjprnId [ 10 + 1]; // ADJPRN_ID VARCHAR(10) NOT NULL, -- 수정자ID

/*
* table column indicator
*/
SQLLEN indtableName ; // TABLE_NAME VARCHAR(128) NOT NULL, -- 테이블명
SQLLEN indindexName ; // INDEX_NAME VARCHAR(128) NOT NULL, -- 인덱스명
SQLLEN indoccupyVol ; // OCCUPY_VOL NUMERIC(15) , -- 점유영역SPACE
SQLLEN indcreatDdtm ; // CREAT_DDTM VARCHAR(14) NOT NULL, -- 생성일시
SQLLEN indcreatrId ; // CREATR_ID VARCHAR(10) NOT NULL, -- 생성자ID
SQLLEN indadjDdtm ; // ADJ_DDTM VARCHAR(14) NOT NULL, -- 수정일시
SQLLEN indadjprnId ; // ADJPRN_ID VARCHAR(10) NOT NULL, -- 수정자ID

} tbsv_sv_gm_data_t;

struct sigaction act_new;
struct sigaction act_old;

void sig_handler( int signo, siginfo_t *siginfo, void *context)
{
printf( "signal received: si_signo(%d), si_errno(%d), si_code(%d)\n", signo, siginfo->si_errno, siginfo->si_code );

switch ( signo )
{
case SIGINT:
case SIGQUIT:
case SIGABRT:
case SIGSEGV:
case SIGTERM:
case SIGUSR2:
printf( "signal received: si_signo(%d), si_errno(%d), si_code(%d)\n", signo, siginfo->si_errno, siginfo->si_code );

/*
if (_st_obj_vol_array.obj != NULL) {
free (_st_obj_vol_array.obj);
_st_obj_vol_array.obj = NULL;
}

if (_st_obj_idx_array.obj != NULL) {
free (_st_obj_idx_array.obj);
_st_obj_idx_array.obj = NULL;
}
*/

if ( gStmt01 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt01 );
if ( gStmt02 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt02 );
if ( gStmt03 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt03 );
if ( gStmt04 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt04 );
if ( gStmt05 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt05 );
if ( gStmt06 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt06 );
if ( gStmt07 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt07 );
if ( gStmt08 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt08 );
if ( gStmt09 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt09 );
if ( gStmt10 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt10 );
if ( gStmt11 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt11 );
if ( gStmt12 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt12 );
if ( gStmt13 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt13 );
if ( gStmt14 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt14 );
if ( gStmt15 != NULL )
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt15 );

if ( gDbc != NULL )
SQLDisconnect( gDbc );

if ( gDbc != NULL )
SQLFreeHandle( SQL_HANDLE_DBC, gDbc );

if ( gEnv != NULL )
SQLFreeHandle( SQL_HANDLE_ENV, gEnv );

break;
}

}

SQLRETURN doJob()
{
int sState = 0;

int i = 0;
int ii = 0;
int jj = 0;
int rowNum = 0;
int exist_yn = 0;

long fetchCount01 = 0;
long fetchCount02 = 0;
long fetchCount03 = 0;
long fetchCount04 = 0;
long fetchCount05 = 0;
long fetchCount06 = 0;
long fetchCount07 = 0;
long fetchCount08 = 0;
long fetchCount09 = 0;
long fetchCount10 = 0;
long fetchCount11 = 0;
long fetchCount12 = 0;
long fetchCount13 = 0;
long fetchCount14 = 0;
long fetchCount15 = 0;

int ret01 = 0;
int ret02 = 0;
int ret03 = 0;
int ret04 = 0;
int ret05 = 0;
int ret06 = 0;
int ret07 = 0;
int ret08 = 0;
int ret09 = 0;
int ret10 = 0;
int ret11 = 0;
int ret12 = 0;
int ret13 = 0;
int ret14 = 0;
int ret15 = 0;

int ARRAY_SIZE_TARGET = 1024; // 기준일(적출대상일) array 초기값 (1024) // today
int ARRAY_SIZE = 10240; // 기간 array 초기값 (10240)
int ARRAY_INC_SIZE = 10240; // 증가값 (10240)

// struct timeval tvBegin; // , tvEnd, tvDiff;

char sSQL01[SQL_BUFF_SIZE] = {0x00,};
char sSQL02[SQL_BUFF_SIZE] = {0x00,};
char sSQL03[SQL_BUFF_SIZE] = {0x00,};
char sSQL04[SQL_BUFF_SIZE] = {0x00,};
char sSQL05[SQL_BUFF_SIZE] = {0x00,};
char sSQL06[SQL_BUFF_SIZE] = {0x00,};
char sSQL07[SQL_BUFF_SIZE] = {0x00,};
char sSQL08[SQL_BUFF_SIZE] = {0x00,};
char sSQL09[SQL_BUFF_SIZE] = {0x00,};
char sSQL10[SQL_BUFF_SIZE] = {0x00,};
char sSQL11[SQL_BUFF_SIZE] = {0x00,};
char sSQL12[SQL_BUFF_SIZE] = {0x00,};
char sSQL13[SQL_BUFF_SIZE] = {0x00,};
char sSQL14[SQL_BUFF_SIZE] = {0x00,};
char sSQL15[SQL_BUFF_SIZE] = {0x00,};

char timeStampBegin[32] = {0x00,};
char timeStampEnd[32] = {0x00,};

/*
************************************************************************
USER DEFINE STRUCTURE

*/

typedef struct {
char tname [128 + 1]; // table name
char name [128 + 1]; // object name (index or table name)
char schema [128 + 1]; // schema name
long vol ; // object size
long freevol ; // object size (free data)
long isum ; // object size (index sum by table)
} st_obj;

// volume of object
typedef struct {
st_obj* obj ;
int cnt ;
} _st_obj_vol;

// index list
typedef struct {
st_obj* obj ;
int cnt ;
} _st_obj_idx;

// 기준일 (적출대상일 )
char targetDate [ 8 + 1] = { 0x00, }; // 적출대상 일자
long indtargetDate ;

x$segment_t x$segment;
tablespace_volume_t tablespace_alloc_size;
space_used_t tablespace_used;

space_used_t table_used;
space_used_t index_used;

// d$tablespace_extent_t mem_undo_tbs; // d$tablespace_extent;
// d$tablespace_extent_t tbs_usr_data;
// d$tablespace_extent_t tbs_usr_indx;
object_t obj_table;
object_t obj_index;

// freeness
d$memory_segment_bitmap_t d$memory_segment_bitmap;
x$session_t x$session;

tbsv_sv_gm_dbf_t tbsv_sv_gm_dbf;
tbsv_sv_gm_data_t tbsv_sv_gm_data;

indtargetDate = getNowDate(targetDate); // 기준일은 TODAY & Previous DAY

// 프로그램 수행 시작 시간
getTimestamp( timeStampBegin );

/*
************************************************************************
STATEMENT HANDLE ALLOCATION
- memory allocation handle

*/
GL_TRY( gStmt01, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt01 ) );
GL_TRY( gStmt02, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt02 ) );
GL_TRY( gStmt03, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt03 ) );
GL_TRY( gStmt04, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt04 ) );
GL_TRY( gStmt05, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt05 ) );
GL_TRY( gStmt06, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt06 ) );
GL_TRY( gStmt07, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt07 ) );
GL_TRY( gStmt08, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt08 ) );
GL_TRY( gStmt09, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt09 ) );
GL_TRY( gStmt10, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt10 ) );
GL_TRY( gStmt11, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt11 ) );
GL_TRY( gStmt12, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt12 ) );
GL_TRY( gStmt13, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt13 ) );
GL_TRY( gStmt14, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt14 ) );
GL_TRY( gStmt15, SQLAllocHandle( SQL_HANDLE_STMT, gDbc, &gStmt15 ) );

sState = 1;

/*
************************************************************************
STATEMENT PREPARE SECTION

*/

sprintf(sSQL01,
" SELECT "
" PHYSICAL_ID, "
" TABLE_ID, "
" TABLE_NAME, "
" SCHEMA_NAME, "
" TABLESPACE_ID "
" FROM DEFINITION_SCHEMA.TABLES A, "
" DEFINITION_SCHEMA.SCHEMATA B "
" WHERE 1=1 "
" AND A.OWNER_ID <> 1 "
" AND A.SCHEMA_ID = B.SCHEMA_ID "
" AND PHYSICAL_ID IS NOT NULL "
" ORDER BY A.TABLESPACE_ID "
);
GL_TRY( gStmt01, SQLPrepare( gStmt01, (SQLCHAR*) sSQL01, SQL_NTS ) );

i = 1;
GL_TRY( gStmt01, SQLBindCol(gStmt01, i++, SQL_C_SBIGINT, &obj_table.physicalId, sizeof(obj_table.physicalId), &obj_table.indphysicalId));
GL_TRY( gStmt01, SQLBindCol(gStmt01, i++, SQL_C_CHAR, obj_table.id, sizeof(obj_table.id), &obj_table.indid) );
GL_TRY( gStmt01, SQLBindCol(gStmt01, i++, SQL_C_CHAR, obj_table.name, sizeof(obj_table.name), &obj_table.indname) );
GL_TRY( gStmt01, SQLBindCol(gStmt01, i++, SQL_C_CHAR, obj_table.schema, sizeof(obj_table.schema), &obj_table.indschema) );
GL_TRY( gStmt01, SQLBindCol(gStmt01, i++, SQL_C_CHAR, obj_table.tbsId, sizeof(obj_table.tbsId), &obj_table.indtbsId) );

sprintf(sSQL02,
" SELECT "
" PHYSICAL_ID, "
" INDEX_ID, "
" INDEX_NAME, "
" SCHEMA_NAME, "
" TABLESPACE_ID "
" FROM INDEXES A, "
" DEFINITION_SCHEMA.SCHEMATA B "
" WHERE 1=1 "
" AND A.OWNER_ID <> 1 "
" AND A.SCHEMA_ID = B.SCHEMA_ID "
" AND INDEX_ID IN "
" ( "
" SELECT INDEX_ID "
" FROM INDEX_KEY_TABLE_USAGE "
" WHERE TABLE_ID = ? "
" ) "
" ORDER BY A.INDEX_ID "
);
GL_TRY( gStmt02, SQLPrepare( gStmt02, (SQLCHAR*) sSQL02, SQL_NTS ) );

i = 1;
GL_TRY( gStmt02, SQLBindCol(gStmt02, i++, SQL_C_SBIGINT, &obj_index.physicalId, sizeof(obj_index.physicalId), &obj_index.indphysicalId));
GL_TRY( gStmt02, SQLBindCol(gStmt02, i++, SQL_C_CHAR, obj_index.id, sizeof(obj_index.id), &obj_index.indid) );
GL_TRY( gStmt02, SQLBindCol(gStmt02, i++, SQL_C_CHAR, obj_index.name, sizeof(obj_index.name), &obj_index.indname) );
GL_TRY( gStmt02, SQLBindCol(gStmt02, i++, SQL_C_CHAR, obj_index.schema, sizeof(obj_index.schema), &obj_index.indschema) );
GL_TRY( gStmt02, SQLBindCol(gStmt02, i++, SQL_C_CHAR, obj_index.tbsId, sizeof(obj_index.tbsId), &obj_index.indtbsId) );

i = 1;
GL_TRY( gStmt02, SQLBindParameter(gStmt02, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(obj_table.id), 0, obj_table.id, sizeof(obj_table.id), &obj_table.indid) );

sprintf(sSQL03,
" SELECT A.NAME, "
" A.ID, "
" TO_CHAR(B.SIZE, '999,999,999,999,990') \"TOTAL SIZE(BYTE)\" "
" FROM X$TABLESPACE A, "
" ( "
" SELECT TABLESPACE_ID, SUM(SIZE) SIZE "
" FROM X$DATAFILE "
" WHERE STATE = 'CREATED' "
" GROUP BY TABLESPACE_ID "
" ) B "
" WHERE 1=1 "
" AND A.ID = B.TABLESPACE_ID "
" ORDER BY A.ID "
);
GL_TRY( gStmt03, SQLPrepare( gStmt03, (SQLCHAR*) sSQL03, SQL_NTS ) );

i = 1;
GL_TRY( gStmt03, SQLBindCol(gStmt03, i++, SQL_C_CHAR, &tablespace_alloc_size.name, sizeof(tablespace_alloc_size.name), &tablespace_alloc_size.indname));
GL_TRY( gStmt03, SQLBindCol(gStmt03, i++, SQL_C_CHAR, &tablespace_alloc_size.id, sizeof(tablespace_alloc_size.id), &tablespace_alloc_size.indid));
GL_TRY( gStmt03, SQLBindCol(gStmt03, i++, SQL_C_CHAR, &tablespace_alloc_size.tot, sizeof(tablespace_alloc_size.tot), &tablespace_alloc_size.indtot));

/*
sprintf(sSQL04,
" SELECT NAME, "
" TO_CHAR(USED_CNT * EXTSIZE, '999,999,999,999,999,990') \"USED SIZE(BYTE)\", "
" TO_CHAR(FREE_CNT * EXTSIZE, '999,999,999,999,999,990') \"FREE SIZE(BYTE)\" "
" FROM ( "
" SELECT COUNT(STATE) USED_CNT "
" FROM D$TABLESPACE_EXTENT('?') "
" WHERE STATE <> 'F' "
" ) A, "
" ( "
" SELECT COUNT(STATE) FREE_CNT "
" FROM D$TABLESPACE_EXTENT('?') "
" WHERE STATE = 'F' "
" ) B, "
" ( "
" SELECT NAME, EXTSIZE "
" FROM X$TABLESPACE "
" WHERE NAME = '?' "
" ) C "
" "
);
GL_TRY( gStmt04, SQLPrepare( gStmt04, (SQLCHAR*) sSQL04, SQL_NTS ) );

i = 1;
GL_TRY( gStmt04, SQLBindCol(gStmt04, i++, SQL_C_CHAR, &tablespace_used.name, sizeof(tablespace_used.name), &tablespace_used.indname));
GL_TRY( gStmt04, SQLBindCol(gStmt04, i++, SQL_C_CHAR, &tablespace_used.used, sizeof(tablespace_used.used), &tablespace_used.indused));
GL_TRY( gStmt04, SQLBindCol(gStmt04, i++, SQL_C_CHAR, &tablespace_used.free, sizeof(tablespace_used.free), &tablespace_used.indfree));

i = 1;
GL_TRY( gStmt04, SQLBindParameter(gStmt04, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tablespace_alloc_size.name), 0, tablespace_alloc_size.name, sizeof(tablespace_alloc_size.name), &tablespace_alloc_size.indname) );
GL_TRY( gStmt04, SQLBindParameter(gStmt04, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tablespace_alloc_size.name), 0, tablespace_alloc_size.name, sizeof(tablespace_alloc_size.name), &tablespace_alloc_size.indname) );
GL_TRY( gStmt04, SQLBindParameter(gStmt04, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tablespace_alloc_size.name), 0, tablespace_alloc_size.name, sizeof(tablespace_alloc_size.name), &tablespace_alloc_size.indname) );
*/

/*
sprintf(sSQL05,
" SELECT "
" ALLOC_PAGE_COUNT * 8192 "
" FROM X$SEGMENT "
" WHERE 1=1 "
" AND PHYSICAL_ID = ? "
);
GL_TRY( gStmt05, SQLPrepare( gStmt05, (SQLCHAR*) sSQL05, SQL_NTS ) );

i = 1;
GL_TRY( gStmt05, SQLBindCol(gStmt05, i++, SQL_C_SBIGINT, &x$segment.allocPageCount, sizeof(x$segment.allocPageCount), &x$segment.indallocPageCount));

i = 1;
GL_TRY( gStmt05, SQLBindParameter(gStmt05, i++, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_BIGINT, 0, 0, &object.physicalId, sizeof(object.physicalId), &object.indphysicalId));

sprintf(sSQL06,
" SELECT "
" STATE "
" FROM D$TABLESPACE_EXTENT('MEM_UNDO_TBS') "
);
GL_TRY( gStmt06, SQLPrepare( gStmt06, (SQLCHAR*) sSQL06, SQL_NTS ) );

i = 1;
// GL_TRY( gStmt06, SQLBindCol(gStmt06, i++, SQL_C_CHAR, &mem_undo_tbs.dumy, sizeof(mem_undo_tbs.dumy), &mem_undo_tbs.inddumy));
*/

/*
SQL_sSQL07
- get free space of object
*/
sprintf(sSQL07,
" SELECT "
" 1 "
" FROM D$MEMORY_SEGMENT_BITMAP('?,?.?') "
" WHERE 1=1 "
" AND FREENESS = 'FR' "
);

// GL_TRY( gStmt07, SQLPrepare( gStmt07, (SQLCHAR*) sSQL07, SQL_NTS ) );
//
// i = 1;
// GL_TRY( gStmt07, SQLBindCol(gStmt07, i++, SQL_C_SBIGINT, &d$tablespace_extent.dumy, sizeof(d$tablespace_extent.dumy), &d$tablespace_extent.inddumy));
//
// i = 1;
// GL_TRY( gStmt07, SQLBindParameter(gStmt07, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(d$memory_segment_bitmap.id), 0, d$memory_segment_bitmap.id, sizeof(d$memory_segment_bitmap.id), &d$memory_segment_bitmap.indid) );
// GL_TRY( gStmt07, SQLBindParameter(gStmt07, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(d$memory_segment_bitmap.name), 0, d$memory_segment_bitmap.name, sizeof(d$memory_segment_bitmap.name), &d$memory_segment_bitmap.indname) );

/*
SQL_sSQL08
- get session list
*/
sprintf(sSQL08,
" SELECT "
" ID, USER_ID, STATUS, SERVER "
" FROM X$SESSION "
);
GL_TRY( gStmt08, SQLPrepare( gStmt08, (SQLCHAR*) sSQL08, SQL_NTS ) );

i = 1;
GL_TRY( gStmt08, SQLBindCol(gStmt08, i++, SQL_C_SBIGINT, &x$session.id, sizeof(x$session.id), &x$session.indid));
GL_TRY( gStmt08, SQLBindCol(gStmt08, i++, SQL_C_CHAR, x$session.username, sizeof(x$session.username), &x$session.indusername) );
GL_TRY( gStmt08, SQLBindCol(gStmt08, i++, SQL_C_CHAR, x$session.status, sizeof(x$session.status), &x$session.indstatus) );
GL_TRY( gStmt08, SQLBindCol(gStmt08, i++, SQL_C_CHAR, x$session.server, sizeof(x$session.server), &x$session.indserver) );

/*
SQL_sSQL09
- DB모니터링DBF 테이블에서 당일 일자에 해당하는 데이터 모두 삭제
*/
sprintf(sSQL09,
" DELETE /*+ index( TBSV_SV_GM_DBF TBSV_SV_GM_DBF_PK_INDEX ) */ "
" FROM TBSV_SV_GM_DBF "
" WHERE 1=1 "
" AND CREAT_DDTM = ? "
);
// GL_TRY( gStmt09, SQLPrepare( gStmt09, (SQLCHAR*) sSQL09, SQL_NTS ) );

i = 1;
// 기준일
GL_TRY( gStmt09, SQLBindParameter(gStmt09, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tbsv_sv_gm_dbf.creatDdtm), 0, tbsv_sv_gm_dbf.creatDdtm, sizeof(tbsv_sv_gm_dbf.creatDdtm), &tbsv_sv_gm_dbf.indcreatDdtm) );

/*
SQL_sSQL10
- DB모니터링DATA 테이블에서 당일 일자에 해당하는 데이터 모두 삭제
*/
sprintf(sSQL10,
" DELETE /*+ index( TBSV_SV_GM_DATA TBSV_SV_GM_DATA_PK_INDEX ) */ "
" FROM TBSV_SV_GM_DATA "
" WHERE 1=1 "
" AND CREAT_DDTM = ? "
);
// GL_TRY( gStmt10, SQLPrepare( gStmt10, (SQLCHAR*) sSQL10, SQL_NTS ) );

i = 1;
// 기준일
GL_TRY( gStmt10, SQLBindParameter(gStmt10, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tbsv_sv_gm_data.creatDdtm), 0, tbsv_sv_gm_data.creatDdtm, sizeof(tbsv_sv_gm_data.creatDdtm), &tbsv_sv_gm_data.indcreatDdtm) );

/*
SQL_sSQL11
- DB모니터링DBF 테이블에 DATA/FREE/UNDO 상태 기록
*/
sprintf(sSQL11,
" INSERT INTO TBSV_SV_GM_DBF ( "
" DATA_VOL , " // DATA_VOL NUMERIC(15) , -- DATA영역SPACE
" FREE_VOL , " // FREE_VOL NUMERIC(15) , -- FREE영역SPACE
" UNDO_VOL , " // UNDO_VOL NUMERIC(15) , -- UNDO영역SPACE
" CREAT_DDTM , " // CREAT_DDTM VARCHAR(14) NOT NULL, -- 생성일시
" CREATR_ID , " // CREATR_ID VARCHAR(10) NOT NULL, -- 생성자ID
" ADJ_DDTM , " // ADJ_DDTM VARCHAR(14) NOT NULL, -- 수정일시
" ADJPRN_ID " // ADJPRN_ID VARCHAR(10) NOT NULL, -- 수정자ID
" ) VALUES ( "
" ?, " // DATA_VOL NUMERIC(15) , -- DATA영역SPACE
" ?, " // FREE_VOL NUMERIC(15) , -- FREE영역SPACE
" ?, " // UNDO_VOL NUMERIC(15) , -- UNDO영역SPACE
" ?, " // CREAT_DDTM VARCHAR(14) NOT NULL, -- 생성일시
" ?, " // CREATR_ID VARCHAR(10) NOT NULL, -- 생성자ID
" ?, " // ADJ_DDTM VARCHAR(14) NOT NULL, -- 수정일시
" ? " // ADJPRN_ID VARCHAR(10) NOT NULL, -- 수정자ID
" ) "
);
// GL_TRY( gStmt11, SQLPrepare( gStmt11, (SQLCHAR*) sSQL11, SQL_NTS ) );

i = 1;
// DATA_VOL NUMERIC(15) , -- DATA영역SPACE
GL_TRY( gStmt11, SQLBindParameter(gStmt11, i++, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_NUMERIC, 15, 0, &tbsv_sv_gm_dbf.dataVol, sizeof(tbsv_sv_gm_dbf.dataVol), &tbsv_sv_gm_dbf.inddataVol) );
// FREE_VOL NUMERIC(15) , -- FREE영역SPACE
GL_TRY( gStmt11, SQLBindParameter(gStmt11, i++, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_NUMERIC, 15, 0, &tbsv_sv_gm_dbf.freeVol, sizeof(tbsv_sv_gm_dbf.freeVol), &tbsv_sv_gm_dbf.indfreeVol) );
// UNDO_VOL NUMERIC(15) , -- UNDO영역SPACE
GL_TRY( gStmt11, SQLBindParameter(gStmt11, i++, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_NUMERIC, 15, 0, &tbsv_sv_gm_dbf.undoVol, sizeof(tbsv_sv_gm_dbf.undoVol), &tbsv_sv_gm_dbf.indundoVol) );
// CREAT_DDTM VARCHAR(14) NOT NULL, -- 생성일시
GL_TRY( gStmt11, SQLBindParameter(gStmt11, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tbsv_sv_gm_dbf.creatDdtm), 0, tbsv_sv_gm_dbf.creatDdtm, sizeof(tbsv_sv_gm_dbf.creatDdtm), &tbsv_sv_gm_dbf.indcreatDdtm));
// CREATR_ID VARCHAR(10) NOT NULL, -- 생성자ID
GL_TRY( gStmt11, SQLBindParameter(gStmt11, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tbsv_sv_gm_dbf.creatrId), 0, tbsv_sv_gm_dbf.creatrId, sizeof(tbsv_sv_gm_dbf.creatrId), &tbsv_sv_gm_dbf.indcreatrId));
// ADJ_DDTM VARCHAR(14) NOT NULL, -- 수정일시
GL_TRY( gStmt11, SQLBindParameter(gStmt11, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tbsv_sv_gm_dbf.adjDdtm), 0, tbsv_sv_gm_dbf.adjDdtm, sizeof(tbsv_sv_gm_dbf.adjDdtm), &tbsv_sv_gm_dbf.indadjDdtm));
// ADJPRN_ID VARCHAR(10) NOT NULL, -- 수정자ID
GL_TRY( gStmt11, SQLBindParameter(gStmt11, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tbsv_sv_gm_dbf.adjprnId), 0, tbsv_sv_gm_dbf.adjprnId, sizeof(tbsv_sv_gm_dbf.adjprnId), &tbsv_sv_gm_dbf.indadjprnId));

/*
SQL_sSQL12
- DB모니터링DATA 테이블에 table/index 상태 기록
*/
sprintf(sSQL12,
" INSERT INTO TBSV_SV_GM_DATA ( "
" TABLE_NAME , " // TABLE_NAME VARCHAR(128) NOT NULL, -- 테이블명
" INDEX_NAME , " // INDEX_NAME VARCHAR(128) NOT NULL, -- 인덱스명
" OCCUPY_VOL , " // OCCUPY_VOL NUMERIC(15) , -- 점유영역SPACE
" CREAT_DDTM , " // CREAT_DDTM VARCHAR(14) NOT NULL, -- 생성일시
" CREATR_ID , " // CREATR_ID VARCHAR(10) NOT NULL, -- 생성자ID
" ADJ_DDTM , " // ADJ_DDTM VARCHAR(14) NOT NULL, -- 수정일시
" ADJPRN_ID " // ADJPRN_ID VARCHAR(10) NOT NULL, -- 수정자ID
" ) VALUES ( "
" ?, " // TABLE_NAME VARCHAR(128) NOT NULL, -- 테이블명
" ?, " // INDEX_NAME VARCHAR(128) NOT NULL, -- 인덱스명
" ?, " // OCCUPY_VOL NUMERIC(15) , -- 점유영역SPACE
" ?, " // CREAT_DDTM VARCHAR(14) NOT NULL, -- 생성일시
" ?, " // CREATR_ID VARCHAR(10) NOT NULL, -- 생성자ID
" ?, " // ADJ_DDTM VARCHAR(14) NOT NULL, -- 수정일시
" ? " // ADJPRN_ID VARCHAR(10) NOT NULL, -- 수정자ID
" ) "
);
// GL_TRY( gStmt12, SQLPrepare( gStmt12, (SQLCHAR*) sSQL12, SQL_NTS ) );

i = 1;
// TABLE_NAME VARCHAR(128) NOT NULL, -- 테이블명
GL_TRY( gStmt12, SQLBindParameter(gStmt12, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tbsv_sv_gm_data.tableName), 0, tbsv_sv_gm_data.tableName, sizeof(tbsv_sv_gm_data.tableName), &tbsv_sv_gm_data.indtableName));
// INDEX_NAME VARCHAR(128) NOT NULL, -- 인덱스명
GL_TRY( gStmt12, SQLBindParameter(gStmt12, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tbsv_sv_gm_data.indexName), 0, tbsv_sv_gm_data.indexName, sizeof(tbsv_sv_gm_data.indexName), &tbsv_sv_gm_data.indindexName));
// OCCUPY_VOL NUMERIC(15) , -- 점유영역SPACE
GL_TRY( gStmt12, SQLBindParameter(gStmt12, i++, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_NUMERIC, 15, 0, &tbsv_sv_gm_data.occupyVol, sizeof(tbsv_sv_gm_data.occupyVol), &tbsv_sv_gm_data.indoccupyVol) );
// CREAT_DDTM VARCHAR(14) NOT NULL, -- 생성일시
GL_TRY( gStmt12, SQLBindParameter(gStmt12, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tbsv_sv_gm_data.creatDdtm), 0, tbsv_sv_gm_data.creatDdtm, sizeof(tbsv_sv_gm_data.creatDdtm), &tbsv_sv_gm_data.indcreatDdtm));
// CREATR_ID VARCHAR(10) NOT NULL, -- 생성자ID
GL_TRY( gStmt12, SQLBindParameter(gStmt12, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tbsv_sv_gm_data.creatrId), 0, tbsv_sv_gm_data.creatrId, sizeof(tbsv_sv_gm_data.creatrId), &tbsv_sv_gm_data.indcreatrId));
// ADJ_DDTM VARCHAR(14) NOT NULL, -- 수정일시
GL_TRY( gStmt12, SQLBindParameter(gStmt12, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tbsv_sv_gm_data.adjDdtm), 0, tbsv_sv_gm_data.adjDdtm, sizeof(tbsv_sv_gm_data.adjDdtm), &tbsv_sv_gm_data.indadjDdtm));
// ADJPRN_ID VARCHAR(10) NOT NULL, -- 수정자ID
GL_TRY( gStmt12, SQLBindParameter(gStmt12, i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(tbsv_sv_gm_data.adjprnId), 0, tbsv_sv_gm_data.adjprnId, sizeof(tbsv_sv_gm_data.adjprnId), &tbsv_sv_gm_data.indadjprnId));

// #ifdef __INFO__
printf("\n");
printf("******************************************************************************\n");
printf("* *\n");
printf("* FREE SPACE IN MEMORY REPORT OF SUNDB DATABASE *\n");
printf("* *\n");
printf("* Copyright 2010-2014, SUNJESOFT Inc. All rights reserved. *\n");
printf("* *\n");
printf("******************************************************************************\n");
// #endif

memset ( &tablespace_alloc_size, 0x00, sizeof(tablespace_volume_t));
memset ( &tablespace_used, 0x00, sizeof(space_used_t));
memset ( &table_used, 0x00, sizeof(space_used_t));
memset ( &index_used, 0x00, sizeof(space_used_t));

memset ( &obj_table, 0x00, sizeof(object_t));
memset ( &obj_index, 0x00, sizeof(object_t));

memset ( &tbsv_sv_gm_dbf, 0x00, sizeof(tbsv_sv_gm_dbf_t));

printf("\n");
printf("------------------------------------------------------------------------------\n");
printf("ID TABLESPACE_NAME TOTAL(BYTE) USED(BYTE) FREE(BYTE)\n");
printf("------------------------------------------------------------------------------\n");
// 0 DICTIONARY_TBS 134,209,536 41,943,040 92,143,616

/*
SQL_sSQL03
*/
GL_TRY( gStmt03, SQLExecute( gStmt03 ) );
fetchCount03 = 0;

while (1) {

ret03 = SQLFetch( gStmt03 );

if ( ret03 == SQL_NO_DATA ) {
// 데이터가 한건도 없으면
if ( fetchCount03 == 0 ) {
printf ("SQL03(%d) TABLESPACE not found.\n", __LINE__);
break;
}
else { // 데이터 1개이상 꺼내고 난후 NO_DATA 이면 꺼낸 데이터 가지고 다음 로직 처리
break;
}
}
GL_TRY( gStmt03, ret03 );

sprintf(sSQL04,
" SELECT NAME, "
" TO_CHAR(USED_CNT * EXTSIZE, '999,999,999,999,990') \"USED SIZE(BYTE)\", "
" TO_CHAR(FREE_CNT * EXTSIZE, '999,999,999,999,990') \"FREE SIZE(BYTE)\" "
" FROM ( "
" SELECT COUNT(STATE) USED_CNT "
" FROM D$TABLESPACE_EXTENT('%s') "
" WHERE STATE <> 'F' "
" ) A, "
" ( "
" SELECT COUNT(STATE) FREE_CNT "
" FROM D$TABLESPACE_EXTENT('%s') "
" WHERE STATE = 'F' "
" ) B, "
" ( "
" SELECT NAME, EXTSIZE "
" FROM X$TABLESPACE "
" WHERE NAME = '%s' "
" ) C "
" "
, tablespace_alloc_size.name
, tablespace_alloc_size.name
, tablespace_alloc_size.name
);
GL_TRY( gStmt04, SQLPrepare( gStmt04, (SQLCHAR*) sSQL04, SQL_NTS ) );

i = 1;
GL_TRY( gStmt04, SQLBindCol(gStmt04, i++, SQL_C_CHAR, &tablespace_used.name, sizeof(tablespace_used.name), &tablespace_used.indname));
GL_TRY( gStmt04, SQLBindCol(gStmt04, i++, SQL_C_CHAR, &tablespace_used.used, sizeof(tablespace_used.used), &tablespace_used.indused));
GL_TRY( gStmt04, SQLBindCol(gStmt04, i++, SQL_C_CHAR, &tablespace_used.free, sizeof(tablespace_used.free), &tablespace_used.indfree));

GL_TRY( gStmt04, SQLExecDirect( gStmt04, (unsigned char*)sSQL04, SQL_NTS ) );
fetchCount04 = 0;

ret04 = SQLFetch( gStmt04 );
if (ret04 == SQL_NO_DATA) {
if ( fetchCount04 == 0 ) { // 데이터가 한건도 없는 경우
memcpy(&tablespace_used.used, "E", 1);
memcpy(&tablespace_used.free, "E", 1);
}
else { // 데이터 1개이상 꺼내고 난후 NO DATA
}
}
GL_TRY( gStmt04, ret04 );
fetchCount04++;
GL_TRY( gStmt04, SQLCloseCursor( gStmt04 ) );

printf("%2s %-32s %19s %19s %19s\n", tablespace_alloc_size.id, tablespace_alloc_size.name, tablespace_alloc_size.tot, tablespace_used.used, tablespace_used.free);

fetchCount03++;
}

GL_TRY( gStmt03, SQLCloseCursor( gStmt03 ) );

/*
************************************************************************
get list

*/

/*
ARRAY_SIZE = 1024;

_st_obj_idx _st_obj_idx_array;
memset ( &_st_obj_idx_array, 0x00, sizeof(_st_obj_idx) );
*/

printf("\n");
printf("------------------------------------------------------------------------------\n");
printf("ID TABLE_NAME TOTAL(BYTE) USED(BYTE) FREE(BYTE)\n");
printf("------------------------------------------------------------------------------\n");

/*
SQL_sSQL01
- table list all
*/

GL_TRY( gStmt01, SQLExecute( gStmt01 ) );
fetchCount01 = 0;

while (1) {

ret01 = SQLFetch( gStmt01 );

if ( ret01 == SQL_NO_DATA ) {
// 데이터가 한건도 없으면
if ( fetchCount01 == 0 ) {
printf ("SQL01(%d) Table not found.\n", __LINE__ );
goto GLIESE_SUCCESS_FINISH;
}
else { // 데이터 1개이상 꺼내고 난후 NO_DATA 이면 꺼낸 데이터 가지고 다음 로직 처리
break;
}
}
GL_TRY( gStmt01, ret01 );
fetchCount01++;

sprintf(sSQL05,
" SELECT TABLE_NAME, TABLESPACE_ID, TOTAL, USED, FREE1 "
" FROM ( "
" SELECT TO_CHAR(SUM(ALLOC_PAGE_COUNT) * 8192, '999,999,999,999,990') TOTAL "
" FROM X$SEGMENT "
" WHERE 1=1 "
" AND PHYSICAL_ID = %ld "
" ) A, "
" ( "
" SELECT TO_CHAR(COUNT(*) * 8192, '999,999,999,999,990') USED "
" FROM D$MEMORY_SEGMENT_BITMAP('TABLE,%s.%s') "
" WHERE 1=1 "
" AND FREENESS <> 'FR' "
" ) B, "
" ( "
" SELECT TO_CHAR(COUNT(*) * 8192, '999,999,999,999,990') FREE1 "
" FROM D$MEMORY_SEGMENT_BITMAP('TABLE,%s.%s') "
" WHERE 1=1 "
" AND FREENESS = 'FR' "
" ) C, "
" ( "
" SELECT TABLE_NAME, TABLESPACE_ID "
" FROM DEFINITION_SCHEMA.TABLES "
" WHERE 1=1 "
" AND PHYSICAL_ID = %ld "
" ) D "
, obj_table.physicalId
, obj_table.schema
, obj_table.name
, obj_table.schema
, obj_table.name
, obj_table.physicalId
);
GL_TRY( gStmt05, SQLPrepare( gStmt05, (SQLCHAR*) sSQL05, SQL_NTS ) );

i = 1;
GL_TRY( gStmt05, SQLBindCol(gStmt05, i++, SQL_C_CHAR, &table_used.name, sizeof(table_used.name), &table_used.indname));
GL_TRY( gStmt05, SQLBindCol(gStmt05, i++, SQL_C_CHAR, &table_used.tbsId, sizeof(table_used.tbsId), &table_used.indtbsId));
GL_TRY( gStmt05, SQLBindCol(gStmt05, i++, SQL_C_CHAR, &table_used.tot, sizeof(table_used.tot), &table_used.indtot));
GL_TRY( gStmt05, SQLBindCol(gStmt05, i++, SQL_C_CHAR, &table_used.used, sizeof(table_used.used), &table_used.indused));
GL_TRY( gStmt05, SQLBindCol(gStmt05, i++, SQL_C_CHAR, &table_used.free, sizeof(table_used.free), &table_used.indfree));

GL_TRY( gStmt05, SQLExecDirect( gStmt05, (unsigned char*)sSQL05, SQL_NTS ) );
fetchCount05 = 0;

ret05 = SQLFetch( gStmt05 );
if (ret05 == SQL_NO_DATA) {
if ( fetchCount05 == 0 ) { // 데이터가 한건도 없는 경우
memcpy(&table_used.tot, "E", 1);
memcpy(&table_used.used, "E", 1);
memcpy(&table_used.free, "E", 1);
}
else { // 데이터 1개이상 꺼내고 난후 NO DATA
}
}
GL_TRY( gStmt05, ret05 );
fetchCount05++;
GL_TRY( gStmt05, SQLCloseCursor( gStmt05 ) );

printf("%2s %-32s %19s %19s %19s \n", table_used.tbsId, table_used.name, table_used.tot, table_used.used, table_used.free);

/*
SQL_sSQL02
- index list all
*/
memset ( &index_used, 0x00, sizeof(space_used_t));

GL_TRY( gStmt02, SQLExecute( gStmt02 ) );
fetchCount02 = 0;

while (1) {

ret02 = SQLFetch( gStmt02 );

if ( ret02 == SQL_NO_DATA ) {
// 데이터가 한건도 없으면
if ( fetchCount02 == 0 ) {
printf (" (has no indexes)\n");
break;
// goto GLIESE_FINISH_LABEL;
}
else { // 데이터 1개이상 꺼내고 난후 NO_DATA 이면 꺼낸 데이터 가지고 다음 로직 처리
break;
}
}
GL_TRY( gStmt02, ret02 );
fetchCount02++;

sprintf(sSQL06,
" SELECT INDEX_NAME, TABLESPACE_ID, TOTAL, USED, FREE1 "
" FROM ( "
" SELECT TO_CHAR(SUM(ALLOC_PAGE_COUNT) * 8192, '999,999,999,999,990') TOTAL "
" FROM X$SEGMENT "
" WHERE 1=1 "
" AND PHYSICAL_ID = %ld "
" ) A, "
" ( "
" SELECT TO_CHAR(COUNT(*) * 8192, '999,999,999,999,990') USED "
" FROM D$MEMORY_SEGMENT_BITMAP('INDEX,%s.%s') "
" WHERE 1=1 "
" AND FREENESS <> 'FR' "
" ) B, "
" ( "
" SELECT TO_CHAR(COUNT(*) * 8192, '999,999,999,999,990') FREE1 "
" FROM D$MEMORY_SEGMENT_BITMAP('INDEX,%s.%s') "
" WHERE 1=1 "
" AND FREENESS = 'FR' "
" ) C, "
" ( "
" SELECT INDEX_NAME, TABLESPACE_ID "
" FROM INDEXES "
" WHERE 1=1 "
" AND PHYSICAL_ID = %ld "
" ) D "
" "
, obj_index.physicalId
, obj_index.schema
, obj_index.name
, obj_index.schema
, obj_index.name
, obj_index.physicalId
);
GL_TRY( gStmt06, SQLPrepare( gStmt06, (SQLCHAR*) sSQL06, SQL_NTS ) );

i = 1;
GL_TRY( gStmt06, SQLBindCol(gStmt06, i++, SQL_C_CHAR, &index_used.name, sizeof(index_used.name), &index_used.indname));
GL_TRY( gStmt06, SQLBindCol(gStmt06, i++, SQL_C_CHAR, &index_used.tbsId, sizeof(index_used.tbsId), &index_used.indtbsId));
GL_TRY( gStmt06, SQLBindCol(gStmt06, i++, SQL_C_CHAR, &index_used.tot, sizeof(index_used.tot), &index_used.indtot));
GL_TRY( gStmt06, SQLBindCol(gStmt06, i++, SQL_C_CHAR, &index_used.used, sizeof(index_used.used), &index_used.indused));
GL_TRY( gStmt06, SQLBindCol(gStmt06, i++, SQL_C_CHAR, &index_used.free, sizeof(index_used.free), &index_used.indfree));

GL_TRY( gStmt06, SQLExecDirect( gStmt06, (unsigned char*)sSQL06, SQL_NTS ) );
fetchCount06 = 0;

ret06 = SQLFetch( gStmt06 );
if (ret06 == SQL_NO_DATA) {
if ( fetchCount06 == 0 ) { // 데이터가 한건도 없는 경우
memcpy(&index_used.tot, "E", 1);
memcpy(&index_used.used, "E", 1);
memcpy(&index_used.free, "E", 1);
}
else { // 데이터 1개이상 꺼내고 난후 NO DATA
}
}
GL_TRY( gStmt06, ret06 );
fetchCount06++;
GL_TRY( gStmt06, SQLCloseCursor( gStmt06 ) );

printf("%2s %-32s %19s %19s %19s \n", index_used.tbsId, index_used.name, index_used.tot, index_used.used, index_used.free);

} // while (1) index
GL_TRY( gStmt02, SQLCloseCursor( gStmt02 ) );

printf("------------------------------------------------------------------------------\n");

} // while (1)
GL_TRY( gStmt01, SQLCloseCursor( gStmt01 ) );

/*
SQL_sSQL09
- DB모니터링DBF 테이블에서 당일 일자에 해당하는 데이터 모두 삭제

DELETE / *+ index( TBSV_SV_GM_DBF TBSV_SV_GM_DBF_PK_INDEX ) * /
FROM TBSV_SV_GM_DBF (DB모니터링DBF)
WHERE 1=1
AND CREAT_DDTM = ?
ret09 = SQLExecute( gStmt09 );
if ( ret09 == SQL_SUCCESS || ret09 == SQL_SUCCESS_WITH_INFO || ret09 == SQL_NO_DATA ) {
// GLIESE_SQL_TRY( SQLEndTran(SQL_HANDLE_DBC, gDbc, SQL_COMMIT));
}
else {
GLIESE_SQL_TRY( SQLEndTran(SQL_HANDLE_DBC, gDbc, SQL_ROLLBACK));
GL_TRY(gStmt09, ret09 );
}

*/

/*
SQL_sSQL11
- DB모니터링DBF 테이블에 DATA/FREE/UNDO 상태 기록

INSERT INTO TBSV_SV_GM_DBF (
DATA_VOL,
FREE_VOL,
UNDO_VOL,
CREAT_DDTM,
CREATR_ID,
ADJ_DDTM,
ADJPRN_ID,
...
ret11 = SQLExecute( gStmt11 );
if ( ret11 == SQL_SUCCESS || ret11 == SQL_SUCCESS_WITH_INFO ) {
// if ( 0 == ii%100 ) {
// GLIESE_SQL_TRY( SQLEndTran(SQL_HANDLE_DBC, gDbc, SQL_COMMIT) );
// }
}
else {
GLIESE_SQL_TRY( SQLEndTran(SQL_HANDLE_DBC, gDbc, SQL_ROLLBACK) );
GL_TRY(gStmt11, ret11 );
}

*/

tbsv_sv_gm_data.indcreatDdtm = getNowDate( (char*)tbsv_sv_gm_data.creatDdtm );
tbsv_sv_gm_data.indcreatrId = sprintf( (char*)tbsv_sv_gm_data.creatrId, "%s", "SYSTEM" );

tbsv_sv_gm_data.indadjDdtm = getNowDate( (char*)tbsv_sv_gm_data.adjDdtm );
tbsv_sv_gm_data.indadjprnId = sprintf( (char*)tbsv_sv_gm_data.adjprnId, "%s", "SYSTEM" );

/*
SQL_sSQL10
- DB모니터링DATA 테이블에서 당일 일자에 해당하는 데이터 모두 삭제

DELETE / *+ index( TBSV_SV_GM_DATA TBSV_SV_GM_DATA_PK_INDEX ) * /
FROM TBSV_SV_GM_DATA (DB모니터링DATA)
WHERE 1=1
AND CREAT_DDTM = ?
ret10 = SQLExecute( gStmt10 );
if ( ret10 == SQL_SUCCESS || ret10 == SQL_SUCCESS_WITH_INFO || ret10 == SQL_NO_DATA ) {
// GLIESE_SQL_TRY( SQLEndTran(SQL_HANDLE_DBC, gDbc, SQL_COMMIT));
}
else {
GLIESE_SQL_TRY( SQLEndTran(SQL_HANDLE_DBC, gDbc, SQL_ROLLBACK));
GL_TRY(gStmt10, ret10 );
}

*/

/*
************************************************************************
STATEMENT HANDLE FREE

*/

/*
if (_st_obj_vol_array.obj != NULL) {
free (_st_obj_vol_array.obj);
_st_obj_vol_array.obj = NULL;
}

if (_st_obj_idx_array.obj != NULL) {
free (_st_obj_idx_array.obj);
_st_obj_idx_array.obj = NULL;
}
*/

GLIESE_SUCCESS;

sState = 0;
GL_TRY( gStmt01, SQLFreeHandle( SQL_HANDLE_STMT, gStmt01 ) );
GL_TRY( gStmt02, SQLFreeHandle( SQL_HANDLE_STMT, gStmt02 ) );
GL_TRY( gStmt03, SQLFreeHandle( SQL_HANDLE_STMT, gStmt03 ) );
GL_TRY( gStmt04, SQLFreeHandle( SQL_HANDLE_STMT, gStmt04 ) );
GL_TRY( gStmt05, SQLFreeHandle( SQL_HANDLE_STMT, gStmt05 ) );
GL_TRY( gStmt06, SQLFreeHandle( SQL_HANDLE_STMT, gStmt06 ) );
GL_TRY( gStmt07, SQLFreeHandle( SQL_HANDLE_STMT, gStmt07 ) );
GL_TRY( gStmt08, SQLFreeHandle( SQL_HANDLE_STMT, gStmt08 ) );
GL_TRY( gStmt09, SQLFreeHandle( SQL_HANDLE_STMT, gStmt09 ) );
GL_TRY( gStmt10, SQLFreeHandle( SQL_HANDLE_STMT, gStmt10 ) );
GL_TRY( gStmt11, SQLFreeHandle( SQL_HANDLE_STMT, gStmt11 ) );
GL_TRY( gStmt12, SQLFreeHandle( SQL_HANDLE_STMT, gStmt12 ) );
GL_TRY( gStmt13, SQLFreeHandle( SQL_HANDLE_STMT, gStmt13 ) );
GL_TRY( gStmt14, SQLFreeHandle( SQL_HANDLE_STMT, gStmt14 ) );
GL_TRY( gStmt15, SQLFreeHandle( SQL_HANDLE_STMT, gStmt15 ) );

gStmt01 = NULL;
gStmt02 = NULL;
gStmt03 = NULL;
gStmt04 = NULL;
gStmt05 = NULL;
gStmt06 = NULL;
gStmt07 = NULL;
gStmt08 = NULL;
gStmt09 = NULL;
gStmt10 = NULL;
gStmt11 = NULL;
gStmt12 = NULL;
gStmt13 = NULL;
gStmt14 = NULL;
gStmt15 = NULL;

// GLIESE_SQL_TRY( SQLEndTran(SQL_HANDLE_DBC, gDbc, SQL_ROLLBACK) );

return (SQL_SUCCESS);

GLIESE_FINISH;

// SQLEndTran(SQL_HANDLE_DBC, gDbc, SQL_ROLLBACK);

/*

if (_st_obj_vol_array.obj != NULL) {
free (_st_obj_vol_array.obj);
_st_obj_vol_array.obj = NULL;
}

if (_st_obj_idx_array.obj != NULL) {
free (_st_obj_idx_array.obj);
_st_obj_idx_array.obj = NULL;
}
*/

switch(sState)
{
case 1:

(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt01 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt02 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt03 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt04 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt05 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt06 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt07 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt08 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt09 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt10 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt11 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt12 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt13 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt14 );
(void) SQLFreeHandle( SQL_HANDLE_STMT, gStmt15 );

gStmt01 = NULL;
gStmt02 = NULL;
gStmt03 = NULL;
gStmt04 = NULL;
gStmt05 = NULL;
gStmt06 = NULL;
gStmt07 = NULL;
gStmt08 = NULL;
gStmt09 = NULL;
gStmt10 = NULL;
gStmt11 = NULL;
gStmt12 = NULL;
gStmt13 = NULL;
gStmt14 = NULL;
gStmt15 = NULL;

/* no break */
default:
break;
}

return (SQL_ERROR);
}

int main(int argc, char* argv[])
{

int sState = 0;
struct timeval tvBegin, tvEnd, tvDiff;

act_new.sa_sigaction = sig_handler;
act_new.sa_flags = SA_SIGINFO;
sigemptyset(&act_new.sa_mask);

if (sigaction(SIGINT, &act_new, &act_old) < 0) {
printf("sigaction SIGINT fail.\n");
return 1;
}
if (sigaction(SIGQUIT, &act_new, &act_old) < 0) {
printf("sigaction SIGQUIT fail.\n");
return 1;
}
if (sigaction(SIGABRT, &act_new, &act_old) < 0) {
printf("sigaction SIGABRT fail.\n");
return 1;
}
if (sigaction(SIGSEGV, &act_new, &act_old) < 0) {
printf("sigaction SIGSEGV fail.\n");
return 1;
}
if (sigaction(SIGTERM, &act_new, &act_old) < 0) {
printf("sigaction SIGTERM fail.\n");
return 1;
}
if (sigaction(SIGUSR2, &act_new, &act_old) < 0) {
printf("sigaction SIGUSR2 fail.\n");
return 1;
}

// begin
gettimeofday(&tvBegin, NULL);

/* If you call SQLAllocEnv() that is included in Gliese ODBC*/
GLIESE_SQL_TRY( SQLAllocHandle( SQL_HANDLE_ENV,
NULL,
&gEnv ) );

sState = 1;
/* SQLSetEnvAttr is sets attributes that govern aspects of environments */
GLIESE_SQL_TRY( SQLSetEnvAttr( gEnv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
0 ) );

/* If you call SQLAllocDbc that is mapped in the Driver Manager to SQLConnect */
GLIESE_SQL_TRY( SQLAllocHandle( SQL_HANDLE_DBC,
gEnv,
&gDbc ) );

sState = 2;
/* SQLConnect establishes connections to a driver and a data source */
GLIESE_SQL_TRY( SQLConnect( gDbc,
(SQLCHAR*)"SUNDB",
SQL_NTS,
(SQLCHAR*)"test",
SQL_NTS,
(SQLCHAR*)"test",
SQL_NTS) );

/* SQLSetConnectAttr sets attributes that govern aspects of connections.
GLIESE_SQL_TRY( SQLSetConnectAttr( gDbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF,
SQL_IS_UINTEGER ) );
*/

sState = 3;

/* If you SQL_SUCCESS that is Select function success */
GLIESE_SQL_TRY( doJob() );
printf("\n");

sState = 2;
/* SQLDisconnect close the connection associated with a specific connection handle */
GLIESE_SQL_TRY( SQLDisconnect( gDbc ) );

sState = 1;
/* SQLFreeHandleDbc frees resources associated with a connection */
GLIESE_SQL_TRY( SQLFreeHandle( SQL_HANDLE_DBC, gDbc ) );

gDbc = NULL;

sState = 0;
/* SQLFreeHandleEnv frees resources associated with a environment */
GLIESE_SQL_TRY( SQLFreeHandle( SQL_HANDLE_ENV, gEnv ) );

gEnv = NULL;

gettimeofday(&tvEnd, NULL);
/*
// insert time diff
timeval_subtract(&tvDiff, &tvEnd, &tvBegin);
timeval_print(&tvBegin);
timeval_print(&tvEnd);
printf("elapsed time: %ld.%06ld\n", tvDiff.tv_sec, tvDiff.tv_usec);

printf("MAIN_SUCCESS\n");
*/

return SQL_SUCCESS;

GLIESE_FINISH;

if( gDbc != NULL)
{
printError( SQL_HANDLE_DBC, gDbc );
}
if( gEnv != NULL)
{
printError( SQL_HANDLE_ENV, gEnv );
}

switch( sState )
{
case 3:
/* SQLDisconnect close the connection associated with a specific connection handle */
(void)SQLDisconnect( gDbc );
/* no break */
case 2:
/* SQLFreeHandleDbc frees resources associated with a connection */
(void)SQLFreeHandle( SQL_HANDLE_DBC, gDbc );
/* no break */
case 1:
/* SQLFreeHandleEnv frees resources associated with a environment */
(void)SQLFreeHandle( SQL_HANDLE_ENV, gEnv );
/* no break */
default:
break;
}

printf("MAIN_CLOSE\n");

return SQL_ERROR;

}

Makefile

TARGET001 = free_space

TARGETS = $(TARGET001)

OBJECT001 = $(TARGET001).o $(OBJS)

OBJECTS = $(OBJECT001)

CC=gcc
MV=mv
AR=ar

LOCAL_HOME=.

LIB= -L$(SUNDB_HOME)/lib -lsundba -lm -lpthread -lrt
INC=
SUNDB_INC=$(SUNDB_HOME)/include

CINC=

CC_OPT=-rdynamic -D__DEBUG__ -D__INFO__ -D__STDOUT__

C_SOURCES = $(OBJECTS:.o=.c)

# SOBJECT = scommon.o scommon2.o batch-common.o
# SOBJECT = malloc.o slog.o
SOBJECT =
SCOM_SRC = $(SOBJECT:.o=.c)

######## implicit rules
#.SUFFIXES: .pc .c

.c.o:
$(CC) -m64 -g -c -o $*.o $*.c -I$(SUNDB_INC) $(CC_OPT) -D_GNU_SOURCE

####### build rules

all:$(TARGETS)

$(TARGET001): $(OBJECT001)
$(CC) -m64 -g -o $(TARGET001) $(OBJECT001) $(SOBJECT) $(LIB) $(CC_OPT)

clean:
rm -f $(TARGETS) $(OBJECTS)

Wonderful tonight

2014년 10월 17일 금요일

ODBC Example (free_space)