What is the alternative for CALL DBC.SYSEXECSQL?

General
Fan

What is the alternative for CALL DBC.SYSEXECSQL?

Hi,

I have a Control table where i store Source DB, Destination DB, Select query. Example







TBL_N SRC_SCMA_N DEST_SCMA_N EXTR_SQL_T
CHBK IVR UTLWORK select * from IVR.CHBK
CO_LOC HRS UTLWORK SELECT * FROM HRS.CO_LOC WHERE CO_LOC_I IN (1, 105, 105, 188, 44, 127)

Before extracting the data from source and  loading into destination. I validate the SQL query in the column EXTR_SQL_T AND set the flag in control table. In next step, i will open a cursor and extract only records whose sql is valid and load the data accordingly.

Problem 1:

FETCH Get_Table_Names INTO TABLES_NAME, SRC_SCH_NAME, DST_SCH_NAME, SQL_QUERY;

CALL DBC.SYSEXECSQL (:SQL_QUERY);

Now i get error message saying "5568 SQL statement is not supported within a stored procedure"

Problem 2:

When i get the table list whose sqls are correct and try to use CALL DBC.SYSEXECSQL, it throws one more error.

FETCH Get_Final_List INTO TABLES_NAME_F, SRC_SCH_NAME_F, DST_SCH_NAME_F, SQL_QUERY_F;

SET Upd_Query='INSERT INTO '||SRC_SCH_NAME_F||'.'||TABLES_NAME_F|| SQL_QUERY_F||';';

CALL DBC.SYSEXECSQL (:Upd_Query);

Kindly provide the solution OR an alternative to achive my task.

Thanks,

RS

 
4 REPLIES
Senior Apprentice

Re: What is the alternative for CALL DBC.SYSEXECSQL?

What's the actual query in :SQL_QUERY?

A SELECT? You can't do a SELECT with sysexecsql. In Td13.10 you could probably OPEN/FETCH it.

And what's the error for problem 2?

Dieter

Fan

Re: What is the alternative for CALL DBC.SYSEXECSQL?

Hi Dieter,

sorry for late reply.

I got solution for problem 1 & 2. Now, i have new problem :)

i am getting error at:

CALL DBC.SYSEXECSQL ('UPDATE TDMUTLWORK.TD_CNTL SET SQL_VLD_STAT_C = "Y" WHERE TBL_N ='||:TABLES_NAME_F||';');

ERROR says: column Y not found in TDMUTLWORK.TD_CNTL. I hope syntax is correct!?

Senior Apprentice

Re: What is the alternative for CALL DBC.SYSEXECSQL?

Double quoted strings are always object names, you need two single quotes instead:

"Y" -> ''Y''

Dieter

Enthusiast

Re: What is the alternative for CALL DBC.SYSEXECSQL?

Hi Sroope,

What's the solution for problem 1?

How to use select in DBC.SYSEXECSQL()? Any other way to attain this? Please help !

Dinesh