473,320 Members | 2,098 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Grouping SQL statements

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
12 5206
Please use below stattements I guess ur SQL will then work.
BEGIN COMPOUND ATOMIC / END COMPOUND

Apr 3 '06 #2
This also gave a syntax error.
Any other options?

Apr 6 '06 #3
use BEGIN COMPOUND ATOMIC / END COMPOUND only

but don't use the COMMIT statement inside this

Apr 7 '06 #4
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
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
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
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
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
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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: JackT | last post by:
Hi, I have the following SQL SELECT Table1.Col1, Table3.Col1 AS Expr1, COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc, FROM Table3 INNER JOIN Table2 ON...
1
by: JC | last post by:
I would like to retrieve 10(dynamic) records of table x (proucts) for each user in table y (users). Can this be done? I would like the end result to be something like this: (would this be a...
1
by: teddysnips | last post by:
Having recently had excellent service here (many thanks, Erland!), here's another wee problem. SQL 2000 I need to get a report which will display the movement of people thus: ID DIRECTION ...
3
by: ahaque38 | last post by:
Hello. Using A2K SP3, I am having the following problem with a report using "Sorting and Grouping". I have recently added a grouping in the reports for "Category2<>'CONTRACTS'". I have...
1
by: dlwilson007 | last post by:
Hello, I am using SQL 2005 Reporting Services to create a user status report. Each page of the report will contain information about 1 user. On a single page, there are 4 different views of...
4
by: mantrid | last post by:
I have records from a database that are extracted with php and displayed in a table. Data in some of the fields is replicated eg something1,item1,somethingelse1 something1,item1,somethingelse2...
2
by: CoopEng | last post by:
Hey Team, Here's one for you all to ponder. In Access 2003, I'm updating a Operations Database for a manufacturing plant. The data is of production runs for the plant. So there are start date's...
2
by: dez5000 | last post by:
I have a query that is pulling a list of patients seen within a certain time frame at certain locations and it also shows whether they had a certain test done. I want to report by location the list...
3
by: JamesDelaney | last post by:
Hello All, I'm new to XSLT and XML, so please excuse the basic question. I'm trying to apply a number of conditional xsl:if statements to xsl:for-each-group, but it does not filter out the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.