|
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. | |
Share:
|
Please use below stattements I guess ur SQL will then work.
BEGIN COMPOUND ATOMIC / END COMPOUND | | |
This also gave a syntax error.
Any other options? | | |
use BEGIN COMPOUND ATOMIC / END COMPOUND only
but don't use the COMMIT statement inside this | | |
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. | | |
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 | | |
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. | | |
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 | | |
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. | | |
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 | | |
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. | | |
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 | | |
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. | | This discussion thread is closed Replies have been disabled for this discussion. Similar topics
5 posts
views
Thread by JackT |
last post: by
|
1 post
views
Thread by JC |
last post: by
|
1 post
views
Thread by teddysnips@hotmail.com |
last post: by
|
3 posts
views
Thread by ahaque38@hotmail.com |
last post: by
| |
4 posts
views
Thread by mantrid |
last post: by
| | | | | | | | | | | | | |