문서유형ㅣ기술정보
분야ㅣ튜닝
문서번호ㅣTTUTI025
개요
multi thread에서 안전하게 내장 SQL (Embedded SQL, ESQL)을 실행하는 방법을 안내합니다.
내장 SQL (Embedded SQL) 을 멀티스레드 환경에서 실행하려면 컴파일 시 옵션과 소스 코드 내 설정이 필요합니다. 이를 통해 여러 스레드에서 안전하게 ESQL을 사용할 수 있습니다.
방법
내장 SQL (Embedded SQL, ESQL) 을 멀티스레드 환경에서 실행하려면 esql precompile 옵션에 THREADS=YES 추가해서 컴파일해야 합니다.
또한 소스 코드에는 EXEC SQL ENABLE THREADS; EXEC SQL CONTEXT USE DEFAULT;를 설정해야합니다.
예제 코드 1
#include <stdio.h>
#include <string.h>
void esql_error(int line_no, int err_no, char *file)
{
printf("esql test esql_error at %d : %d in %s\n", line_no, err_no, file);
}
void
main(void)
{
char *uid = "tibero";
char *pwd = "tmax";
int balance = 0;
sql_context ctx1;
sql_context ctx2;
printf("***** thread context test start *****\n\n");
EXEC SQL WHENEVER SQLERROR DO esql_error(__LINE__, sqlca.sqlcode, __FILE__);
EXEC SQL ENABLE THREADS;
EXEC SQL CONTEXT USE DEFAULT;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
EXEC SQL WHENEVER SQLERROR continue;
EXEC SQL DROP TABLE ACCOUNT;
EXEC SQL CREATE TABLE ACCOUNT (
ACCOUNT_ID CHAR(14),
BRANCH_CD CHAR(4),
CUSTOMER_ID CHAR(13),
ACCOUNT_TYPE CHAR(4),
BALANCE NUMBER(15, 5),
CREATE_DATE DATE,
EXPIRY_DATE DATE
);
EXEC SQL WHENEVER SQLERROR DO esql_error(__LINE__, sqlca.sqlcode, __FILE__);
EXEC SQL INSERT INTO account
VALUES ('ACCOUNT_ID_001', 'B003', 'CUSTOMER_ID_0',
'A002', 500000, '2003-10-5', '2005-10-5');
EXEC SQL INSERT INTO account
VALUES ('ACCOUNT_ID_002', 'B003', 'CUSTOMER_ID_1',
'A001', 10000, '2003-12-25', '2010-12-25');
EXEC SQL INSERT INTO account
VALUES ('ACCOUNT_ID_003', 'B001', 'CUSTOMER_ID_2',
'A003', 50000000, '1998-9-1', '2005-9-1');
EXEC SQL COMMIT WORK;
EXEC SQL CONTEXT ALLOCATE :ctx1;
EXEC SQL CONTEXT ALLOCATE :ctx2;
EXEC SQL CONTEXT USE :ctx1;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
EXEC SQL SELECT BALANCE INTO :balance FROM ACCOUNT
WHERE ACCOUNT_ID = 'ACCOUNT_ID_001';
if (balance != 500000)
printf("expected : 500000, real : %d\n", balance);
EXEC SQL CONTEXT USE :ctx2;
EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
EXEC SQL SELECT BALANCE INTO :balance FROM ACCOUNT
WHERE ACCOUNT_ID = 'ACCOUNT_ID_002';
if (balance != 10000)
printf("expected : 10000, real : %d\n", balance);
EXEC SQL CONTEXT FREE :ctx1;
EXEC SQL CONTEXT FREE :ctx2;
EXEC SQL CONTEXT USE DEFAULT;
EXEC SQL SELECT BALANCE INTO :balance FROM ACCOUNT
WHERE ACCOUNT_ID = 'ACCOUNT_ID_002';
if (balance != 10000)
printf("expected : 10000, real : %d\n", balance);
printf("***** thread context test end *****\n");
}
예제 코드 2
int Fn_DRFT_SYNC_Make_LoadFile(char *p_exp_type)
{
FRM_ERR("=========LOAD START==========");
char s_load_path [ 255+1];
char s_load_msg [ 255+1];
char s_file_type [ 3+1];
char s_exp_type [ 1+1]; //EXPORT 대상 TABLE 구분
char *conn_str = "msscm/msscm";
SQLRETURN n_ret = SQL_SUCCESS;
SQLINTEGER versionNumber = 1;
SQLCHAR dataFileName[256];
SQLCHAR actionString[256];
SQLCHAR msgFileName[256];
SQLCHAR fieldTerm[5];
SQLCHAR lineTerm[5];
SQLCHAR enclStart[5];
SQLCHAR enclEnd[5];
struct sqlca ca = {"\0", 0, 0, {0, "\0"}, "\0", {0, 0, 0, 0, 0, 0}, "\0", "\0"};
FRM_ERR("=========LOAD 1==========");
TBExportStruct exportStruct = {NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL};
TBExportIn exportIn = {{NULL, 0, NULL, 0, NULL, 0, NULL, 0, NULL, 0}};
TBExportOut exportOut = {0};
EXEC SQL ENABLE THREADS;
EXEC SQL CONTEXT USE DEFAULT;
EXEC SQL CONNECT :conn_str;
n_ret = TBConnect((SQLCHAR *)DNS_NAME, (SQLCHAR *)USER_NAME, (SQLCHAR *)PWD, &ca);
if(n_ret != SQL_SUCCESS) return -1;
//EXPORT 할 대상 TABLE 구분 - D:DATA 테이블, S:SUM 테이블
memset (s_exp_type , 0x00 , sizeof(s_exp_type));
strcpy (s_exp_type , p_exp_type);
//EXPORT FILE TYPE 정의 - DEL타입 강제 세팅
//memset (s_file_type , 0x00 , sizeof(s_file_type));
//memcpy (s_file_type , "DEL" , sizeof(s_file_type)-1);
FRM_ERR("=========LOAD 2==========");
//EXPORT SQL 세팅
memset (g_export_sql, 0x00 , sizeof(g_export_sql ));
sprintf(g_export_sql, "SELECT * FROM %s", g_GLB_TABLE);
if ( s_exp_type[0] == 'D' ) {
//EXPORT DATA FILE PATH 설정
memset (s_load_path, 0x00, sizeof( s_load_path ) );
sprintf(s_load_path, "%s/%.4s%.8s_%.2s.DEL", DRFT_LOAD_PATH , g_orgcode, g_sysdate,
g_sync_seq );
//EXPORT MSG PATH 설정
memset (s_load_msg , 0x00, sizeof( s_load_msg ) );
sprintf(s_load_msg , "%s/%.4s%.8s_%.2s.MSG", DRFT_LOAD_PATH , g_orgcode, g_sysdate,
g_sync_seq );
}
else {
//EXPORT FILE PATH 설정
memset (s_load_path, 0x00, sizeof( s_load_path ) );
sprintf(s_load_path,
"%s/%.4s%.8s_SUM_%.2s.DEL",
g_sysdate, g_sync_seq );
//EXPORT MSG PATH 설정
memset (s_load_msg , 0x00, sizeof( s_load_msg ) );
sprintf(s_load_msg
,
DRFT_LOAD_PATH
,
g_orgcode,
"%s/%.4s%.8s_SUM_%.2s.MSG", DRFT_LOAD_PATH , g_orgcode,
g_sysdate, g_sync_seq );
}
strcpy((char *)dataFileName, s_load_path);
strcpy((char *)actionString, g_export_sql);
strcpy((char *)msgFileName, s_load_msg);
strcpy((char *)fieldTerm, ",");
strcpy((char *)lineTerm, "\n");
strcpy((char *)enclStart, "\"");
strcpy((char *)enclEnd, "\"");
FRM_ERR("=========LOAD 3==========");
/* setting data file name */
exportStruct.piDataFileName = dataFileName;
exportStruct.iDataFileNameLen = strlen((char *)dataFileName);
FRM_ERR("=========LOAD 4==========");
/* setting action String */
// exportStruct.piActionString = mfrm_cb_malloc(1024+sizeof(sqlstr));
exportStruct.piActionString = (struct sqlchar *)mfrm_cb_malloc( 1024 + sizeof( struct
sqlchar));
exportStruct.piActionString->data = actionString;
exportStruct.piActionString->length = strlen((char *)actionString);
/* setting file type : s_file_type DEL 강제 설정 */
exportStruct.iFileType = SQL_DEL;
FRM_ERR("=========LOAD 5==========");
/* setting message file name */
exportStruct.piMsgFileName = msgFileName;
exportStruct.iMsgFileNameLen = strlen((char *)msgFileName);
/* setting field term, line term etc.. SQL_METH_D에 따라 설정이 필요. */
exportIn.iMeta.fieldTerm = fieldTerm;
exportIn.iMeta.fieldTermLen = strlen((char *)fieldTerm);
exportIn.iMeta.lineTerm = lineTerm;
exportIn.iMeta.lineTermLen = strlen((char *)lineTerm);
exportIn.iMeta.enclStart = enclStart;
exportIn.iMeta.enclStartLen = strlen((char *)enclStart);
exportIn.iMeta.enclEnd = enclEnd;
exportIn.iMeta.enclEndLen = strlen((char *)enclEnd);
FRM_ERR("=========LOAD 6==========");
/* setting export input, output information */
exportStruct.piExportInfoIn = &exportIn;
exportStruct.poExportInfoOut = &exportOut;
FRM_ERR("=========LOAD 7==========");
FRM_ERR("n_ret : %d", n_ret);
/* setting file type */
n_ret = TBExport(versionNumber, &exportStruct, &ca);
FRM_ERR("=========LOAD 8==========");
if (n_ret != SQL_SUCCESS) {
FRM_ERR("[%s]EXPORT
ERROR!!![%d][%d]\n[%s]",
s_load_path,
n_ret,
SQLCODE,
g_export_sql );
g_sqlcode = SQLCODE;
return -1;
}
FRM_ERR("=========LOAD 9==========");
FRM_DSP("[%.4s][%.8s][%.2s][ROWS EXPORT]:[%ld]", g_orgcode, g_sysdate, g_sync_seq,
exportOut.oRowsExported );
/* disconnect */
n_ret = TBDisconnect((SQLCHAR *)DNS_NAME, &ca);
if(n_ret != SQL_SUCCESS) return -1;
EXEC SQL COMMIT WORK RELEASE;
return 0;
}
===========================================================
EXEC SQL ENABLE THREADS;
EXEC SQL CONTEXT USE DEFAULT;
EXEC SQL CONNECT :"[userid]/[pw]";