Grouping SQL statements

shsandeep

hi all,
i want to group few sql statements into one and run them in DataStage.
However, i am getting the following error:

BEGIN
DELETE FROM DEVSTGEV.STG_EV_RSKV_IO;
COMMIT;
INSERT INTO DEVSTGEV.STG_EV_RSKV_IO(L45__HEADER, COMPANY_CODE, POLICY_BR,
POLICY_NO, POLICY_TYP, RISK_NO, SEQ_NO__IO, TERM_ID, TRAN_DATE, TRAN_TIME,
TRAN_USER, VALID_FLAG, RISK_CLASS, DATE_EFFECT, DATE_TERM, PAGE_NUMBER,
HEAD_NO, GEN_NO, RATING_FLAG, CATEGORY, COVER_01, COVER_02, COVER_03,
COVER_04, COVER_05, DESC_01, DESC_02, DESC_03, DESC_04, DESC_05,
SUM_INS_01, SUM_INS_02, SUM_INS_03, SUM_INS_04, SUM_INS_05, PREMIUM_01,
PREMIUM_02, PREMIUM_03, PREMIUM_04, PREMIUM_05, RRN__IO) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
END;

SQLSTATE=37000, DBMS.CODE=-104
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol
driver][UDB DB2 for Windows, UNIX, and Linux]ILLEGAL SYMBOL FROM; VALID
SYMBOLS ARE BEGIN DELETE
Any suggestions!

Cheers,
San.

Apr 3 '06 #1

m_v_ratnam

Please use below stattements I guess ur SQL will then work.
BEGIN COMPOUND ATOMIC / END COMPOUND

Apr 3 '06 #2

shsandeep

This also gave a syntax error.
Any other options?

Apr 6 '06 #3

situ

use BEGIN COMPOUND ATOMIC / END COMPOUND only

but don't use the COMMIT statement inside this

Apr 7 '06 #4

shsandeep

I tried the following compound statement within DataStage application but
gave the following syntax errors:

1.
BEGIN COMPOUND ATOMIC
ALTER TABLE DEVSTGEV.STG_EV_BTRN_02 ACTIVATE NOT LOGGED INITIALLY;
DELETE FROM DEVSTGEV.STG_EV_BTRN_02;
INSERT INTO DEVSTGEV.STG_EV_BTRN_02(L45__HEADER, COMPANY_CODE, POLICY_BR,
POLICY_NO, POLICY_TYP, SEQ_NO__02, BATCH_BR, BATCH_ACCT_YR,
BATCH_ACCT_MTH, BATCH_TRAN_TYPE, BATCH_NO, RI_ACCOUNT_BR, RI_ACCOUNT_NO,
TERM_ID, TRAN_DATE, TRAN_TIME, TRAN_USER, VALID_FLAG, CATEGORY, RISK_NO,
TRAN_TYPE, SUB_CLASS, FIRE_BRIG, DATE_EFF, DATE_EXP, JNL_REF, TRAN_DESC,
PREM_EX, GR_PREM, FSL, ST_DUTY, COMM, TOT_PREM, EX_CHG1, CH_TYP1, EX_CHG2,
CH_TYP2, GR_SUMINS, PROPN, RI_AC_TYP, DATE_INC, RISK_CLS, TRANS_NO,
STAT_CD, RSK_STATE, RI_METHOD, RI_TRANO, RI_WOC_NO, COMM_RATE, GST_COMM,
FXOL_SEQ, GL_ORIGIN, TERR_ZONE, RRN__02) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?);

END COMPOUND
SQLSTATE=37000, DBMS.CODE=-104
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol
driver][UDB DB2 for Windows, UNIX, and Linux]ILLEGAL SYMBOL BEGIN COMPOUND
ATOMIC ALTER ; VALID SYMBOLS ARE BEGIN-OF-STATEMENT

2.
BEGIN COMPOUND
ALTER TABLE DEVSTGEV.STG_EV_BTRN_02 ACTIVATE NOT LOGGED INITIALLY;
DELETE FROM DEVSTGEV.STG_EV_BTRN_02;
INSERT INTO DEVSTGEV.STG_EV_BTRN_02(L45__HEADER, COMPANY_CODE, POLICY_BR,
POLICY_NO, POLICY_TYP, SEQ_NO__02, BATCH_BR, BATCH_ACCT_YR,
BATCH_ACCT_MTH, BATCH_TRAN_TYPE, BATCH_NO, RI_ACCOUNT_BR, RI_ACCOUNT_NO,
TERM_ID, TRAN_DATE, TRAN_TIME, TRAN_USER, VALID_FLAG, CATEGORY, RISK_NO,
TRAN_TYPE, SUB_CLASS, FIRE_BRIG, DATE_EFF, DATE_EXP, JNL_REF, TRAN_DESC,
PREM_EX, GR_PREM, FSL, ST_DUTY, COMM, TOT_PREM, EX_CHG1, CH_TYP1, EX_CHG2,
CH_TYP2, GR_SUMINS, PROPN, RI_AC_TYP, DATE_INC, RISK_CLS, TRANS_NO,
STAT_CD, RSK_STATE, RI_METHOD, RI_TRANO, RI_WOC_NO, COMM_RATE, GST_COMM,
FXOL_SEQ, GL_ORIGIN, TERR_ZONE, RRN__02) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?);

END COMPOUND

SQLSTATE=37000, DBMS.CODE=-104
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol
driver][UDB DB2 for Windows, UNIX, and Linux]ILLEGAL SYMBOL BEGIN COMPOUND
ALTER TABLE D; VALID SYMBOLS ARE BEGIN-OF-STATEMENT

Any suggestions will be helpful.
Thanks.

Cheers,
San.

Apr 19 '06 #5

Serge Rielau

shsandeep wrote:

I tried the following compound statement within DataStage application but
gave the following syntax errors:

1.
BEGIN COMPOUND ATOMIC
ALTER TABLE DEVSTGEV.STG_EV_BTRN_02 ACTIVATE NOT LOGGED INITIALLY;
DELETE FROM DEVSTGEV.STG_EV_BTRN_02;
INSERT INTO DEVSTGEV.STG_EV_BTRN_02(L45__HEADER, COMPANY_CODE, POLICY_BR,
POLICY_NO, POLICY_TYP, SEQ_NO__02, BATCH_BR, BATCH_ACCT_YR,
BATCH_ACCT_MTH, BATCH_TRAN_TYPE, BATCH_NO, RI_ACCOUNT_BR, RI_ACCOUNT_NO,
TERM_ID, TRAN_DATE, TRAN_TIME, TRAN_USER, VALID_FLAG, CATEGORY, RISK_NO,
TRAN_TYPE, SUB_CLASS, FIRE_BRIG, DATE_EFF, DATE_EXP, JNL_REF, TRAN_DESC,
PREM_EX, GR_PREM, FSL, ST_DUTY, COMM, TOT_PREM, EX_CHG1, CH_TYP1, EX_CHG2,
CH_TYP2, GR_SUMINS, PROPN, RI_AC_TYP, DATE_INC, RISK_CLS, TRANS_NO,
STAT_CD, RSK_STATE, RI_METHOD, RI_TRANO, RI_WOC_NO, COMM_RATE, GST_COMM,
FXOL_SEQ, GL_ORIGIN, TERR_ZONE, RRN__02) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?);

END COMPOUND
SQLSTATE=37000, DBMS.CODE=-104
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol
driver][UDB DB2 for Windows, UNIX, and Linux]ILLEGAL SYMBOL BEGIN COMPOUND
ATOMIC ALTER ; VALID SYMBOLS ARE BEGIN-OF-STATEMENT

2.
BEGIN COMPOUND
ALTER TABLE DEVSTGEV.STG_EV_BTRN_02 ACTIVATE NOT LOGGED INITIALLY;
DELETE FROM DEVSTGEV.STG_EV_BTRN_02;
INSERT INTO DEVSTGEV.STG_EV_BTRN_02(L45__HEADER, COMPANY_CODE, POLICY_BR,
POLICY_NO, POLICY_TYP, SEQ_NO__02, BATCH_BR, BATCH_ACCT_YR,
BATCH_ACCT_MTH, BATCH_TRAN_TYPE, BATCH_NO, RI_ACCOUNT_BR, RI_ACCOUNT_NO,
TERM_ID, TRAN_DATE, TRAN_TIME, TRAN_USER, VALID_FLAG, CATEGORY, RISK_NO,
TRAN_TYPE, SUB_CLASS, FIRE_BRIG, DATE_EFF, DATE_EXP, JNL_REF, TRAN_DESC,
PREM_EX, GR_PREM, FSL, ST_DUTY, COMM, TOT_PREM, EX_CHG1, CH_TYP1, EX_CHG2,
CH_TYP2, GR_SUMINS, PROPN, RI_AC_TYP, DATE_INC, RISK_CLS, TRANS_NO,
STAT_CD, RSK_STATE, RI_METHOD, RI_TRANO, RI_WOC_NO, COMM_RATE, GST_COMM,
FXOL_SEQ, GL_ORIGIN, TERR_ZONE, RRN__02) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?);

END COMPOUND

SQLSTATE=37000, DBMS.CODE=-104
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol
driver][UDB DB2 for Windows, UNIX, and Linux]ILLEGAL SYMBOL BEGIN COMPOUND
ALTER TABLE D; VALID SYMBOLS ARE BEGIN-OF-STATEMENT

Compound statement is supported in EMBEDDED SQL only (C, Cobol, ...)
May I ask why you need to group?
Does your table have always one row (delete 1 row, insert 1 row)?
You could just run an UPDATE statement in that case.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 19 '06 #6

shsandeep

No, obviously not.
The table sometimes has millions of rows.
The DataStage job first needs to empty the table and then load all the
rows from the file.
In this process, it fills up the log files.
So, the aim of the compound SQL is to turn off logging before it deletes
and inserts any data.

What could be the best solution?

Thanks for the help.

Cheers,
San.

Apr 19 '06 #7

Serge Rielau

shsandeep wrote:

No, obviously not.
The table sometimes has millions of rows.
The DataStage job first needs to empty the table and then load all the
rows from the file.
In this process, it fills up the log files.
So, the aim of the compound SQL is to turn off logging before it deletes
and inserts any data.

I don't know what the DB2 interface is with datastage.
But I seem to recall that jd_12345 knows datastage.
Now how do we lure him back?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 20 '06 #8

shsandeep

Interface between DB2 and DataStage here is via ODBC.
Hope I have understood your question properly.

If somehow, the logging is turned off using these compound sql statements,
the problem would be resolved.

thanks.

cheers,
San.

Apr 20 '06 #9

Serge Rielau

Well I got that.
But can you throw in more than one statement?
I mean if you can then simply run the statements one at a time...
But you appear to be bent to package it up into one statement.
Which makes me think... throw the SQL into a procedure and simply do a
CALL statement.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 20 '06 #10

shsandeep

Not able to throw more than one statement.
So, the only option left is to call a procedure from DataStage.
But I have never written a stored procedure before.
Can you please guide to me through that process?
The syntax, where to write, how to compile, run, call, etc.?
The table name would be passed as an argument.

Thanks.

Cheers,
San.

Apr 20 '06 #11

Serge Rielau

shsandeep wrote:

Not able to throw more than one statement.
So, the only option left is to call a procedure from DataStage.
But I have never written a stored procedure before.
Can you please guide to me through that process?
The syntax, where to write, how to compile, run, call, etc.?
The table name would be passed as an argument.

Look in the sqllib\samples directory.
Sorry.. I gladly help, but I expect those I help to do the basic research.
You will need:
CREATE PROCEDURE
PREPARE and EXECUTE
and CALL

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 20 '06 #12

jd_12345

Unfortunately, DataStage & DataStage TX (my specialty) are totally
unrelated products having originated from two separate companies. In my
experience, DSTX is most successful when using standard and rather
simple SQL queries/updates as it is most commonly used in a
transaction-oriented environment rather than an ETL one like DataStage.
If more complex SQL-based applications are required, they are usually
broken into separate steps and the results passed from step to step..

Regards, Jim D.

Apr 20 '06 #13