Problem executing multiple sql statements in same sql file

Jim Aloye

Problem executing multiple sql statements in same sql file

As someone just getting into DB2 (Most of what I do is with MS SQL) I am encountering an interesting issue and even after having google searched it a bit, I am unable to find an explanation of why its happening or a way to resolve it.

I am hoping an expert here may be able to provide some insight, advice, and/or a possible solution to it.  

I am connected to DB2 for iSeries 7.2 via ODBC using Idera DB Artisan

I can query fine, create tables fine, create temporary tables fine, etc... provided I only do one task per command.

If I try to do several things sequentially in a single SQL query as  shown below, I get errors:

 

DECLARE GLOBAL TEMPORARY TABLE MYCASE ( CASE_ID VARCHAR(50)

,DOCKET_CENTURY VARCHAR(2)

,LEGACY_DOCKET_CENTURY VARCHAR(1)

)

 

INSERT INTO SESSION.MYCASE

(

SELECT

CRIMAS.AACPCMS AS [CASE_ID]

,CASE

WHEN CRIPRI.BDCSCD = 0 THEN '19'

WHEN CRIPRI.BDCSCD = 1 THEN '20'

WHEN CRIPRI.BDCSCD = 2 THEN '21'

WHEN CRIPRI.BDCSCD = 9 THEN '09'

END AS DOCKET_CENTURY

,CRIPRI.BDCSCD AS LEGACY_DOCKET_CENTURY

FROM CRIMINAL.CCP042 CRIPRI

INNER JOIN CRIMINAL.CPP100 CRIMAS ON CRIMAS.AACSCD = CRIPRI.BDCSCD AND CRIMAS.AACSYR = CRIPRI.BDCSYR AND CRIMAS.AACSSQ = CRIPRI.BDCSSQ

INNER JOIN CRIMINAL.CPP170 CRIPTY ON CRIMAS.AACPCMS = CRIPTY.CPCPCMS

INNER JOIN CRIMINAL.CPP170 BONDPTY ON CRIMAS.AACPCMS = BONDPTY.CPCPCMS

WHERE (CRIPRI.BDCODE = 0173 AND CRIPRI.BDCSSQ > 0 AND CRIPRI.BDAMT > 0 AND CRIPTY.CPROLE = 'Defendant' AND CRIPTY.CPNAME IS NOT NULL

AND ((BONDPTY.CPROLE = 'Bail Payor' OR BONDPTY.CPROLE = 'Bondsman' OR BONDPTY.CPROLE = 'Private Surety' OR BONDPTY.CPROLE = 'Surety' ) AND CRIPRI.BDCODE = 0173 AND CRIPRI.BDCSSQ > 0 AND CRIPRI.BDAMT > 0))

)

 

SELECT * FROM SESSION.MYCASE

 

If I execute each of those manually and individually I can get it to work.

But manually performing a 3 step separate execution will not help with what I need to do. I need to run a single SQL script to create the temporary table, then insert data into it, and then query it.

I tried adding BEGIN at the top and END at the bottom of the whole thing,  but that just yields different errors.

In MS SQL Server, I can do this all day long without any problem, but it seems to be very different in DB2.

 

If anyone can provide some insight and/or a recommendation on why this is happening and how I might change things around to fix it so it runs as a single Ad-Hoc SQL Query, that would be greatly appreciated.

 

 

Jay Reavill

Problem executing multiple sql statements in same sql file
(in response to Jim Aloye)
Have you tried putting semi-colons at the end of each statement?

Thanks,
Jay

Jay Reavill
Database Administrator
NACard Solutions
• Office: 727.227.2144
• Cell: 727.215.5794
•: [login to unmask email]<mailto:[login to unmask email]>
FIS | Empowering the Financial World [cid:[login to unmask email] https://www.facebook.com/FIStoday [cid:[login to unmask email] https://twitter.com/FISGlobal [cid:[login to unmask email] https://www.linkedin.com/company/fis

From: Jim Aloye [mailto:[login to unmask email]
Sent: Wednesday, June 21, 2017 1:18 PM
To: [login to unmask email]
Subject: [DB2-L] - Problem executing multiple sql statements in same sql file


As someone just getting into DB2 (Most of what I do is with MS SQL) I am encountering an interesting issue and even after having google searched it a bit, I am unable to find an explanation of why its happening or a way to resolve it.

I am hoping an expert here may be able to provide some insight, advice, and/or a possible solution to it.

I am connected to DB2 for iSeries 7.2 via ODBC using Idera DB Artisan

I can query fine, create tables fine, create temporary tables fine, etc... provided I only do one task per command.

If I try to do several things sequentially in a single SQL query as shown below, I get errors:



DECLARE GLOBAL TEMPORARY TABLE MYCASE ( CASE_ID VARCHAR(50)

,DOCKET_CENTURY VARCHAR(2)

,LEGACY_DOCKET_CENTURY VARCHAR(1)

)



INSERT INTO SESSION.MYCASE

(

SELECT

CRIMAS.AACPCMS AS [CASE_ID]

,CASE

WHEN CRIPRI.BDCSCD = 0 THEN '19'

WHEN CRIPRI.BDCSCD = 1 THEN '20'

WHEN CRIPRI.BDCSCD = 2 THEN '21'

WHEN CRIPRI.BDCSCD = 9 THEN '09'

END AS DOCKET_CENTURY

,CRIPRI.BDCSCD AS LEGACY_DOCKET_CENTURY

FROM CRIMINAL.CCP042 CRIPRI

INNER JOIN CRIMINAL.CPP100 CRIMAS ON CRIMAS.AACSCD = CRIPRI.BDCSCD AND CRIMAS.AACSYR = CRIPRI.BDCSYR AND CRIMAS.AACSSQ = CRIPRI.BDCSSQ

INNER JOIN CRIMINAL.CPP170 CRIPTY ON CRIMAS.AACPCMS = CRIPTY.CPCPCMS

INNER JOIN CRIMINAL.CPP170 BONDPTY ON CRIMAS.AACPCMS = BONDPTY.CPCPCMS

WHERE (CRIPRI.BDCODE = 0173 AND CRIPRI.BDCSSQ > 0 AND CRIPRI.BDAMT > 0 AND CRIPTY.CPROLE = 'Defendant' AND CRIPTY.CPNAME IS NOT NULL

AND ((BONDPTY.CPROLE = 'Bail Payor' OR BONDPTY.CPROLE = 'Bondsman' OR BONDPTY.CPROLE = 'Private Surety' OR BONDPTY.CPROLE = 'Surety' ) AND CRIPRI.BDCODE = 0173 AND CRIPRI.BDCSSQ > 0 AND CRIPRI.BDAMT > 0))

)



SELECT * FROM SESSION.MYCASE



If I execute each of those manually and individually I can get it to work.

But manually performing a 3 step separate execution will not help with what I need to do. I need to run a single SQL script to create the temporary table, then insert data into it, and then query it.

I tried adding BEGIN at the top and END at the bottom of the whole thing, but that just yields different errors.

In MS SQL Server, I can do this all day long without any problem, but it seems to be very different in DB2.



If anyone can provide some insight and/or a recommendation on why this is happening and how I might change things around to fix it so it runs as a single Ad-Hoc SQL Query, that would be greatly appreciated.





-----End Original Message-----
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
Attachments

  • image001.png (<1k)
  • image002.png (<1k)
  • image003.png (<1k)

Robert Plata

Problem executing multiple sql statements in same sql file
(in response to Jay Reavill)
Agree - semi-colons. I would also use iSeries Navigator instead of DB Artisan, but that is my opinion.


Robert Plata
Database Administrator
Driver and Motor Vehicle Services
Oregon Department of Transportation



From: Reavill, Jay [mailto:[login to unmask email]
Sent: Wednesday, June 21, 2017 10:21 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Problem executing multiple sql statements in same sql file

Have you tried putting semi-colons at the end of each statement?

Thanks,
Jay

Jay Reavill
Database Administrator
NACard Solutions
• Office: 727.227.2144
• Cell: 727.215.5794
•: [login to unmask email]<mailto:[login to unmask email]>
FIS | Empowering the Financial World [cid:[login to unmask email] https://www.facebook.com/FIStoday [cid:[login to unmask email] https://twitter.com/FISGlobal [cid:[login to unmask email] https://www.linkedin.com/company/fis

From: Jim Aloye [mailto:[login to unmask email]
Sent: Wednesday, June 21, 2017 1:18 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Problem executing multiple sql statements in same sql file


As someone just getting into DB2 (Most of what I do is with MS SQL) I am encountering an interesting issue and even after having google searched it a bit, I am unable to find an explanation of why its happening or a way to resolve it.

I am hoping an expert here may be able to provide some insight, advice, and/or a possible solution to it.

I am connected to DB2 for iSeries 7.2 via ODBC using Idera DB Artisan

I can query fine, create tables fine, create temporary tables fine, etc... provided I only do one task per command.

If I try to do several things sequentially in a single SQL query as shown below, I get errors:



DECLARE GLOBAL TEMPORARY TABLE MYCASE ( CASE_ID VARCHAR(50)

,DOCKET_CENTURY VARCHAR(2)

,LEGACY_DOCKET_CENTURY VARCHAR(1)

)



INSERT INTO SESSION.MYCASE

(

SELECT

CRIMAS.AACPCMS AS [CASE_ID]

,CASE

WHEN CRIPRI.BDCSCD = 0 THEN '19'

WHEN CRIPRI.BDCSCD = 1 THEN '20'

WHEN CRIPRI.BDCSCD = 2 THEN '21'

WHEN CRIPRI.BDCSCD = 9 THEN '09'

END AS DOCKET_CENTURY

,CRIPRI.BDCSCD AS LEGACY_DOCKET_CENTURY

FROM CRIMINAL.CCP042 CRIPRI

INNER JOIN CRIMINAL.CPP100 CRIMAS ON CRIMAS.AACSCD = CRIPRI.BDCSCD AND CRIMAS.AACSYR = CRIPRI.BDCSYR AND CRIMAS.AACSSQ = CRIPRI.BDCSSQ

INNER JOIN CRIMINAL.CPP170 CRIPTY ON CRIMAS.AACPCMS = CRIPTY.CPCPCMS

INNER JOIN CRIMINAL.CPP170 BONDPTY ON CRIMAS.AACPCMS = BONDPTY.CPCPCMS

WHERE (CRIPRI.BDCODE = 0173 AND CRIPRI.BDCSSQ > 0 AND CRIPRI.BDAMT > 0 AND CRIPTY.CPROLE = 'Defendant' AND CRIPTY.CPNAME IS NOT NULL

AND ((BONDPTY.CPROLE = 'Bail Payor' OR BONDPTY.CPROLE = 'Bondsman' OR BONDPTY.CPROLE = 'Private Surety' OR BONDPTY.CPROLE = 'Surety' ) AND CRIPRI.BDCODE = 0173 AND CRIPRI.BDCSSQ > 0 AND CRIPRI.BDAMT > 0))

)



SELECT * FROM SESSION.MYCASE



If I execute each of those manually and individually I can get it to work.

But manually performing a 3 step separate execution will not help with what I need to do. I need to run a single SQL script to create the temporary table, then insert data into it, and then query it.

I tried adding BEGIN at the top and END at the bottom of the whole thing, but that just yields different errors.

In MS SQL Server, I can do this all day long without any problem, but it seems to be very different in DB2.



If anyone can provide some insight and/or a recommendation on why this is happening and how I might change things around to fix it so it runs as a single Ad-Hoc SQL Query, that would be greatly appreciated.





-----End Original Message-----
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
-----End Original Message-----

Jim Aloye

RE: Problem executing multiple sql statements in same sql file
(in response to Robert Plata)

When I add semicolons to DB Artisan, the same problem occurs.

However, if I use the "Run a SQL Script" Query Tool from within iSeries Navigator and add SemiColons to the complete statement with all three parts works great!

I will open a ticket with DB Artisan to see if I can get to the bottom of the problem as used in Artisan and will post the results here in the event it is helpful to someone else.

Jim Aloye

RE: Problem executing multiple sql statements in same sql file
(in response to Jim Aloye)

The plot thickens. It seems that ANY Tool I use to run this query (except using Run a SQL Script from iSeries Navigator) has the same problem. I need to use an ETL Tool to set up a nightly data extraction job, but it seems all tools that use iSeries Access ODBC Driver experience the same type of problem.

Here is an example of the same problem occurring inside of Scribe Insight


 

I am having problems getting scribe to pull data from DB2 for iSeries 7.2

I am using Scribe InSight version 7.9.1.56452

The following query works PERFECT in the “Run A SQL Script” tool within iSeries Navigator.

DECLARE GLOBAL TEMPORARY TABLE MYCASE

   (

       CASE_ID VARCHAR(25)

       ,DOCKET_CENTURY VARCHAR(2)

       ,LEGACY_DOCKET_CENTURY VARCHAR(1)

       ,DOCKET_YEAR VARCHAR(2)

       ,LEGACY_DOCKET_YEAR VARCHAR(2)

       ,DOCKET_FULLYEAR VARCHAR(4)

       ,DOCKET_SEQUENCE VARCHAR(6)

       ,LEGACY_DOCKET_SEQUENCE VARCHAR(6)

       ,DJ_NUMBER VARCHAR(2)

       ,LEGACY_DJ_NUMBER VARCHAR(2)

       ,DJ_CENTURY VARCHAR(2)

       ,LEGACY_DJ_CENTURY VARCHAR(1)

       ,DJ_YEAR VARCHAR(2)

       ,LEGACY_DJ_YEAR VARCHAR(2)

       ,DJ_FULLYEAR VARCHAR(4)

   );

INSERT INTO SESSION.MYCASE (

           SELECT CRIMAS.AACPCMS AS CASE_ID

           ,CASE

               WHEN CRIPRI.BDCSCD = 0 THEN '19'

               WHEN CRIPRI.BDCSCD = 1 THEN '20'

               WHEN CRIPRI.BDCSCD = 2 THEN '21'

               WHEN CRIPRI.BDCSCD = 9 THEN '09'

           END AS DOCKET_CENTURY

           ,CRIPRI.BDCSCD AS LEGACY_DOCKET_CENTURY

           ,RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDCSYR)), 2) AS DOCKET_YEAR

         ,CRIPRI.BDCSYR AS LEGACY_DOCKET_YEAR

         ,CASE

               WHEN CRIPRI.BDCSCD = 0 THEN '19' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDCSYR)), 2)

               WHEN CRIPRI.BDCSCD = 1 THEN '20' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDCSYR)), 2)

               WHEN CRIPRI.BDCSCD = 2 THEN '21' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDCSYR)), 2)

               WHEN CRIPRI.BDCSCD = 9 THEN '09'

           END AS DOCKET_FULLYEAR

           ,RIGHT(REPEAT('000000', 6) || RTRIM(CHAR(CRIPRI.BDCSSQ)), 6) AS DOCKET_SEQUENCE

           ,CRIPRI.BDCSSQ AS LEGACY_DOCKET_SEQUENCE

           ,RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJNO)), 2) AS DJ_NUMBER

           ,CRIPRI.BDDJNO AS LEGACY_DJ_NUMBER

           ,CASE

               WHEN CRIPRI.BDDJCT = 0 THEN '19'

               WHEN CRIPRI.BDDJCT = 1 THEN '20'

               WHEN CRIPRI.BDDJCT = 2 THEN '21'

               WHEN CRIPRI.BDDJCT = 9 THEN '09'

           END AS DJ_CENTURY

           ,CRIPRI.BDDJCT AS LEGACY_DJ_CENTURY

            ,RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJYR)), 2) AS DJ_YEAR

           ,CRIPRI.BDDJYR AS LEGACY_DJ_YEAR

           ,CASE

               WHEN CRIPRI.BDDJCT = 0 THEN '19' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJYR)), 2)

               WHEN CRIPRI.BDDJCT = 1 THEN '20' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJYR)), 2)

               WHEN CRIPRI.BDDJCT = 2 THEN '21' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJYR)), 2)

               WHEN CRIPRI.BDDJCT = 9 THEN '09'

           END AS DJ_FULLYEAR

               FROM CRIMINAL.CCP042 CRIPRI

               INNER JOIN CRIMINAL.CPP100 CRIMAS ON CRIMAS.AACSCD = CRIPRI.BDCSCD AND CRIMAS.AACSYR = CRIPRI.BDCSYR AND CRIMAS.AACSSQ = CRIPRI.BDCSSQ

               INNER JOIN CRIMINAL.CPP170 CRIPTY ON CRIMAS.AACPCMS = CRIPTY.CPCPCMS

               INNER JOIN CRIMINAL.CPP170 BONDPTY ON CRIMAS.AACPCMS = BONDPTY.CPCPCMS

               WHERE (CRIPRI.BDCODE = 0173 AND CRIPRI.BDCSSQ > 0 AND CRIPRI.BDAMT > 0 AND CRIPTY.CPROLE = 'Defendant' AND CRIPTY.CPNAME IS NOT NULL

               AND ((BONDPTY.CPROLE = 'Bail Payor' OR BONDPTY.CPROLE = 'Bondsman' OR BONDPTY.CPROLE = 'Private Surety' OR BONDPTY.CPROLE = 'Surety' ) AND CRIPRI.BDCODE = 0173 AND CRIPRI.BDCSSQ > 0 AND CRIPRI.BDAMT > 0))

       );

     SELECT * FROM SESSION.MYCASE;

 

Unfortunately, as soon as I run the exact same query inside of Scribe InSight, I get the following error:

[42000] SQL call failed. SQL0104 - Token ; was not valid. Valid tokens: <END-OF-STATEMENT>.

Has anyone seen such a problem before?

I can’t get this query to work in Scribe InSight no matter what I try. 

Other queries work, like real simple ones such as select x from y. 

Does anyone know why this query doesn’t work? This is the only type of query we run, so not being able to run these queries is a serious problem.


 

And the same type of problem occurs in DBArtisan.

[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ; was not valid. Valid tokens: <END-OF-STATEMENT>.(42000,-104)



 

 

I am starting to believe this problem is more of a DB2 problem than a problem with any specific tool since both Idera and Scribe are coming back and saying the problem is not their software, and claiming since queries work but not this query, the problem is the query.

Sadly that is not an acceptable response. I am hoping someone here encountered this before and can shed some light on what may be causing this problem.

Sam Baugh

Problem executing multiple sql statements in same sql file
(in response to Jim Aloye)
Just guessing, but it looks like it is not recognizing the ";" as a
statement delimiter, perhaps there is a some setting for this.

On Thu, Jun 22, 2017 at 9:32 AM, Jim Aloye <[login to unmask email]> wrote:

> ------------------------------
>
> The plot thickens. It seems that ANY Tool I use to run this query (except
> using Run a SQL Script from iSeries Navigator) has the same problem. I need
> to use an ETL Tool to set up a nightly data extraction job, but it seems
> all tools that use iSeries Access ODBC Driver experience the same type of
> problem.
>
> Here is an example of the same problem occurring inside of Scribe Insight
> ------------------------------
>
>
>
> I am having problems getting scribe to pull data from DB2 for iSeries 7.2
>
> I am using Scribe InSight version 7.9.1.56452
>
> The following query works PERFECT in the “Run A SQL Script” tool within
> iSeries Navigator.
>
> DECLARE GLOBAL TEMPORARY TABLE MYCASE
>
> (
>
> CASE_ID VARCHAR(25)
>
> ,DOCKET_CENTURY VARCHAR(2)
>
> ,LEGACY_DOCKET_CENTURY VARCHAR(1)
>
> ,DOCKET_YEAR VARCHAR(2)
>
> ,LEGACY_DOCKET_YEAR VARCHAR(2)
>
> ,DOCKET_FULLYEAR VARCHAR(4)
>
> ,DOCKET_SEQUENCE VARCHAR(6)
>
> ,LEGACY_DOCKET_SEQUENCE VARCHAR(6)
>
> ,DJ_NUMBER VARCHAR(2)
>
> ,LEGACY_DJ_NUMBER VARCHAR(2)
>
> ,DJ_CENTURY VARCHAR(2)
>
> ,LEGACY_DJ_CENTURY VARCHAR(1)
>
> ,DJ_YEAR VARCHAR(2)
>
> ,LEGACY_DJ_YEAR VARCHAR(2)
>
> ,DJ_FULLYEAR VARCHAR(4)
>
> );
>
> INSERT INTO SESSION.MYCASE (
>
> SELECT CRIMAS.AACPCMS AS CASE_ID
>
> ,CASE
>
> WHEN CRIPRI.BDCSCD = 0 THEN '19'
>
> WHEN CRIPRI.BDCSCD = 1 THEN '20'
>
> WHEN CRIPRI.BDCSCD = 2 THEN '21'
>
> WHEN CRIPRI.BDCSCD = 9 THEN '09'
>
> END AS DOCKET_CENTURY
>
> ,CRIPRI.BDCSCD AS LEGACY_DOCKET_CENTURY
>
> ,RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDCSYR)), 2) AS
> DOCKET_YEAR
>
> ,CRIPRI.BDCSYR AS LEGACY_DOCKET_YEAR
>
> ,CASE
>
> WHEN CRIPRI.BDCSCD = 0 THEN '19' || RIGHT(REPEAT('00', 2)
> || RTRIM(CHAR(CRIPRI.BDCSYR)), 2)
>
> WHEN CRIPRI.BDCSCD = 1 THEN '20' || RIGHT(REPEAT('00', 2)
> || RTRIM(CHAR(CRIPRI.BDCSYR)), 2)
>
> WHEN CRIPRI.BDCSCD = 2 THEN '21' || RIGHT(REPEAT('00', 2)
> || RTRIM(CHAR(CRIPRI.BDCSYR)), 2)
>
> WHEN CRIPRI.BDCSCD = 9 THEN '09'
>
> END AS DOCKET_FULLYEAR
>
> ,RIGHT(REPEAT('000000', 6) || RTRIM(CHAR(CRIPRI.BDCSSQ)), 6) AS
> DOCKET_SEQUENCE
>
> ,CRIPRI.BDCSSQ AS LEGACY_DOCKET_SEQUENCE
>
> ,RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJNO)), 2) AS
> DJ_NUMBER
>
> ,CRIPRI.BDDJNO AS LEGACY_DJ_NUMBER
>
> ,CASE
>
> WHEN CRIPRI.BDDJCT = 0 THEN '19'
>
> WHEN CRIPRI.BDDJCT = 1 THEN '20'
>
> WHEN CRIPRI.BDDJCT = 2 THEN '21'
>
> WHEN CRIPRI.BDDJCT = 9 THEN '09'
>
> END AS DJ_CENTURY
>
> ,CRIPRI.BDDJCT AS LEGACY_DJ_CENTURY
>
> ,RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJYR)), 2) AS
> DJ_YEAR
>
> ,CRIPRI.BDDJYR AS LEGACY_DJ_YEAR
>
> ,CASE
>
> WHEN CRIPRI.BDDJCT = 0 THEN '19' || RIGHT(REPEAT('00', 2)
> || RTRIM(CHAR(CRIPRI.BDDJYR)), 2)
>
> WHEN CRIPRI.BDDJCT = 1 THEN '20' || RIGHT(REPEAT('00', 2)
> || RTRIM(CHAR(CRIPRI.BDDJYR)), 2)
>
> WHEN CRIPRI.BDDJCT = 2 THEN '21' || RIGHT(REPEAT('00', 2)
> || RTRIM(CHAR(CRIPRI.BDDJYR)), 2)
>
> WHEN CRIPRI.BDDJCT = 9 THEN '09'
>
> END AS DJ_FULLYEAR
>
> FROM CRIMINAL.CCP042 CRIPRI
>
> INNER JOIN CRIMINAL.CPP100 CRIMAS ON CRIMAS.AACSCD =
> CRIPRI.BDCSCD AND CRIMAS.AACSYR = CRIPRI.BDCSYR AND CRIMAS.AACSSQ =
> CRIPRI.BDCSSQ
>
> INNER JOIN CRIMINAL.CPP170 CRIPTY ON CRIMAS.AACPCMS =
> CRIPTY.CPCPCMS
>
> INNER JOIN CRIMINAL.CPP170 BONDPTY ON CRIMAS.AACPCMS =
> BONDPTY.CPCPCMS
>
> WHERE (CRIPRI.BDCODE = 0173 AND CRIPRI.BDCSSQ > 0 AND
> CRIPRI.BDAMT > 0 AND CRIPTY.CPROLE = 'Defendant' AND CRIPTY.CPNAME IS NOT
> NULL
>
> AND ((BONDPTY.CPROLE = 'Bail Payor' OR BONDPTY.CPROLE =
> 'Bondsman' OR BONDPTY.CPROLE = 'Private Surety' OR BONDPTY.CPROLE =
> 'Surety' ) AND CRIPRI.BDCODE = 0173 AND CRIPRI.BDCSSQ > 0 AND CRIPRI.BDAMT
> > 0))
>
> );
>
> SELECT * FROM SESSION.MYCASE;
>
>
>
> Unfortunately, as soon as I run the exact same query inside of Scribe
> InSight, I get the following error:
>
> *[42000] SQL call failed. SQL0104 - Token ; was not valid. Valid tokens: .*
>
> Has anyone seen such a problem before?
>
> I can’t get this query to work in Scribe InSight no matter what I try.
>
> Other queries work, like real simple ones such as select x from y.
>
> Does anyone know why this query doesn’t work? This is the only type of
> query we run, so not being able to run these queries is a serious problem.
> ------------------------------
>
>
>
> And the same type of problem occurs in DBArtisan.
>
> [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ; was not
> valid. Valid tokens: .(42000,-104)
> ------------------------------
> ------------------------------
>
>
>
>
>
> I am starting to believe this problem is more of a DB2 problem than a
> problem with any specific tool since both Idera and Scribe are coming back
> and saying the problem is not their software, and claiming since queries
> work but not this query, the problem is the query.
>
> Sadly that is not an acceptable response. I am hoping someone here
> encountered this before and can shed some light on what may be causing this
> problem.
>
> -----End Original Message-----
>

Robert Krall

RE: Problem executing multiple sql statements in same sql file
(in response to Sam Baugh)

Have you tried a '#' sign as your delimiter?  I have not used this product before to run SQL, but in all the tools I have used for DB2 I have set the delimiter inside the settings of the application. I would suggest looking around there.  This was a pretty big learning curve for me as well coming from the MSSQL world and not worrying about delimiters ever. 

Jim Tonchick

Problem executing multiple sql statements in same sql file
(in response to Jim Aloye)
Have you tried just running just the SELECT statement imbedded in the INSERT statement instead of trying to use the temporary table?


-----Original Message-----
From: Jim Aloye <[login to unmask email]>
To: DB2-L <[login to unmask email]>
Sent: Thu, Jun 22, 2017 09:32 AM
Subject: [DB2-L] - RE: Problem executing multiple sql statements in same sql file



<div id="AOLMsgPart_2_c91b3fd3-edd8-4523-8b9a-a0c981a33b8b">
<div class="aolReplacedBody"><hr>
<p>The plot thickens. It seems that ANY Tool I use to run this query (except using Run a SQL Script from iSeries Navigator) has the same problem. I need to use an ETL Tool to set up a nightly data extraction job, but it seems all tools that use iSeries Access ODBC Driver experience the same type of problem.</p>
<p>Here is an example of the same problem occurring inside of Scribe Insight</p>
<hr>
<p> </p>
<p>I am having problems getting scribe to pull data from DB2 for iSeries 7.2</p>
<p>I am using Scribe InSight version 7.9.1.56452</p>
<p>The following query works PERFECT in the “Run A SQL Script” tool within iSeries Navigator.</p>
<p>DECLARE GLOBAL TEMPORARY TABLE MYCASE</p>
<p> (</p>
<p> CASE_ID VARCHAR(25)</p>
<p> ,DOCKET_CENTURY VARCHAR(2)</p>
<p> ,LEGACY_DOCKET_CENTURY VARCHAR(1)</p>
<p> ,DOCKET_YEAR VARCHAR(2)</p>
<p> ,LEGACY_DOCKET_YEAR VARCHAR(2)</p>
<p> ,DOCKET_FULLYEAR VARCHAR(4)</p>
<p> ,DOCKET_SEQUENCE VARCHAR(6)</p>
<p> ,LEGACY_DOCKET_SEQUENCE VARCHAR(6)</p>
<p> ,DJ_NUMBER VARCHAR(2)</p>
<p> ,LEGACY_DJ_NUMBER VARCHAR(2)</p>
<p> ,DJ_CENTURY VARCHAR(2)</p>
<p> ,LEGACY_DJ_CENTURY VARCHAR(1)</p>
<p> ,DJ_YEAR VARCHAR(2)</p>
<p> ,LEGACY_DJ_YEAR VARCHAR(2)</p>
<p> ,DJ_FULLYEAR VARCHAR(4)</p>
<p> );</p>
<p>INSERT INTO SESSION.MYCASE (</p>
<p> SELECT CRIMAS.AACPCMS AS CASE_ID</p>
<p> ,CASE</p>
<p> WHEN CRIPRI.BDCSCD = 0 THEN '19'</p>
<p> WHEN CRIPRI.BDCSCD = 1 THEN '20'</p>
<p> WHEN CRIPRI.BDCSCD = 2 THEN '21'</p>
<p> WHEN CRIPRI.BDCSCD = 9 THEN '09'</p>
<p> END AS DOCKET_CENTURY</p>
<p> ,CRIPRI.BDCSCD AS LEGACY_DOCKET_CENTURY</p>
<p> ,RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDCSYR)), 2) AS DOCKET_YEAR</p>
<p> ,CRIPRI.BDCSYR AS LEGACY_DOCKET_YEAR</p>
<p> ,CASE</p>
<p> WHEN CRIPRI.BDCSCD = 0 THEN '19' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDCSYR)), 2)</p>
<p> WHEN CRIPRI.BDCSCD = 1 THEN '20' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDCSYR)), 2)</p>
<p> WHEN CRIPRI.BDCSCD = 2 THEN '21' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDCSYR)), 2)</p>
<p> WHEN CRIPRI.BDCSCD = 9 THEN '09'</p>
<p> END AS DOCKET_FULLYEAR</p>
<p> ,RIGHT(REPEAT('000000', 6) || RTRIM(CHAR(CRIPRI.BDCSSQ)), 6) AS DOCKET_SEQUENCE</p>
<p> ,CRIPRI.BDCSSQ AS LEGACY_DOCKET_SEQUENCE</p>
<p> ,RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJNO)), 2) AS DJ_NUMBER</p>
<p> ,CRIPRI.BDDJNO AS LEGACY_DJ_NUMBER</p>
<p> ,CASE</p>
<p> WHEN CRIPRI.BDDJCT = 0 THEN '19'</p>
<p> WHEN CRIPRI.BDDJCT = 1 THEN '20'</p>
<p> WHEN CRIPRI.BDDJCT = 2 THEN '21'</p>
<p> WHEN CRIPRI.BDDJCT = 9 THEN '09'</p>
<p> END AS DJ_CENTURY</p>
<p> ,CRIPRI.BDDJCT AS LEGACY_DJ_CENTURY</p>
<p> ,RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJYR)), 2) AS DJ_YEAR</p>
<p> ,CRIPRI.BDDJYR AS LEGACY_DJ_YEAR</p>
<p> ,CASE</p>
<p> WHEN CRIPRI.BDDJCT = 0 THEN '19' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJYR)), 2)</p>
<p> WHEN CRIPRI.BDDJCT = 1 THEN '20' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJYR)), 2)</p>
<p> WHEN CRIPRI.BDDJCT = 2 THEN '21' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJYR)), 2)</p>
<p> WHEN CRIPRI.BDDJCT = 9 THEN '09'</p>
<p> END AS DJ_FULLYEAR</p>
<p> FROM CRIMINAL.CCP042 CRIPRI</p>
<p> INNER JOIN CRIMINAL.CPP100 CRIMAS ON CRIMAS.AACSCD = CRIPRI.BDCSCD AND CRIMAS.AACSYR = CRIPRI.BDCSYR AND CRIMAS.AACSSQ = CRIPRI.BDCSSQ</p>
<p> INNER JOIN CRIMINAL.CPP170 CRIPTY ON CRIMAS.AACPCMS = CRIPTY.CPCPCMS</p>
<p> INNER JOIN CRIMINAL.CPP170 BONDPTY ON CRIMAS.AACPCMS = BONDPTY.CPCPCMS</p>
<p> WHERE (CRIPRI.BDCODE = 0173 AND CRIPRI.BDCSSQ > 0 AND CRIPRI.BDAMT > 0 AND CRIPTY.CPROLE = 'Defendant' AND CRIPTY.CPNAME IS NOT NULL</p>
<p> AND ((BONDPTY.CPROLE = 'Bail Payor' OR BONDPTY.CPROLE = 'Bondsman' OR BONDPTY.CPROLE = 'Private Surety' OR BONDPTY.CPROLE = 'Surety' ) AND CRIPRI.BDCODE = 0173 AND CRIPRI.BDCSSQ > 0 AND CRIPRI.BDAMT > 0))</p>
<p> );</p>
<p> SELECT * FROM SESSION.MYCASE;</p>
<p> </p>
<p>Unfortunately, as soon as I run the exact same query inside of Scribe InSight, I get the following error:</p>
<p><strong>[42000] SQL call failed. SQL0104 - Token ; was not valid. Valid tokens: .</strong></p>
<p>Has anyone seen such a problem before?</p>
<p>I can’t get this query to work in Scribe InSight no matter what I try. </p>
<p>Other queries work, like real simple ones such as select x from y. </p>
<p>Does anyone know why this query doesn’t work? This is the only type of query we run, so not being able to run these queries is a serious problem.</p>
<hr>
<p> </p>
<p>And the same type of problem occurs in DBArtisan.</p>
<p>[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ; was not valid. Valid tokens: .(42000,-104)</p>
<hr><hr>
<p> </p>
<p> </p>
<p>I am starting to believe this problem is more of a DB2 problem than a problem with any specific tool since both Idera and Scribe are coming back and saying the problem is not their software, and claiming since queries work but not this query, the problem is the query.</p>
<p>Sadly that is not an acceptable response. I am hoping someone here encountered this before and can shed some light on what may be causing this problem.</p>
<hr size="1" style="color:#ccc"><div id="aolmail_socfooter" style="font-size:80%"><span style="font-weight:bold">Site Links: </span>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/fo/st/?post=181793&anc=p181793#p181793">View post online</a>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/fo/si/?topic=19">View mailing list online</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]">Start new thread via email</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]?Subject=Unsubscribe">Unsubscribe from this mailing list</a>
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/us/to/">Manage your subscription</a>


This email has been sent to: <a href="mailto:[login to unmask email]">[login to unmask email]</a>
<p>
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU

ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See

<a target="_blank" rel="noopener noreferrer" href="http://www.ESAIGroup.com/idug">http://www.ESAIGroup.com/idug</a>

</p>
<p>
Use of this email content is governed by the terms of service at:
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/cm/ld/fid=2">http://www.idug.org/p/cm/ld/fid=2</a></p>

</div><hr size="1" style="color:#ccc"></div>
</div>

James Campbell

Problem executing multiple sql statements in same sql file
(in response to Jim Tonchick)
Because there are three SQL statements.

The tool that presents an SQL statement is responsible for handling multiple statements
strung together. It appears that DB2 for iSeries treats a terminating semi-colon as a syntax
error and expects the tool that presents the statement to have stripped the semi-colon, and
whatever follows, off.

Check with the tool documentation - it is possible that you have not defined a semi-colon as
the statement terminator.

Which , perhaps , Run a SQL Script from iSeries Navigator has.

You say that 'select x from y' works - but does 'select x from y ; '?

James Campbell


On 22 Jun 2017 at 20:01, Jim Tonchick wrote:

>
> Have you tried just running just the SELECT statement imbedded in the INSERT statement
> instead of trying to use the temporary table?
>
>
> -----Original Message-----
> From: Jim Aloye <[login to unmask email]>
> To: DB2-L <[login to unmask email]>
> Sent: Thu, Jun 22, 2017 09:32 AM
> Subject: [DB2-L] - RE: Problem executing multiple sql statements in same sql file
>
>
>
> The plot thickens. It seems that ANY Tool I use to run this query (except using Run a SQL Script
> from iSeries Navigator) has the same problem. I need to use an ETL Tool to set up a nightly data
> extraction job, but it seems all tools that use iSeries Access ODBC Driver experience the same
> type of problem.
> Here is an example of the same problem occurring inside of Scribe Insight
>
>  
> I am having problems getting scribe to pull data from DB2 for iSeries 7.2
> I am using Scribe InSight version 7.9.1.56452
> The following query works PERFECT in the "Run A SQL Script" tool within iSeries Navigator.
> DECLARE GLOBAL TEMPORARY TABLE MYCASE
>    (
>        CASE_ID VARCHAR(25)
>        ,DOCKET_CENTURY VARCHAR(2)
>        ,LEGACY_DOCKET_CENTURY VARCHAR(1)
>        ,DOCKET_YEAR VARCHAR(2)
>        ,LEGACY_DOCKET_YEAR VARCHAR(2)
>        ,DOCKET_FULLYEAR VARCHAR(4)
>        ,DOCKET_SEQUENCE VARCHAR(6)
>        ,LEGACY_DOCKET_SEQUENCE VARCHAR(6)
>        ,DJ_NUMBER VARCHAR(2)
>        ,LEGACY_DJ_NUMBER VARCHAR(2)
>        ,DJ_CENTURY VARCHAR(2)
>        ,LEGACY_DJ_CENTURY VARCHAR(1)
>        ,DJ_YEAR VARCHAR(2)
>        ,LEGACY_DJ_YEAR VARCHAR(2)
>        ,DJ_FULLYEAR VARCHAR(4)
>    );
> INSERT INTO SESSION.MYCASE (
>            SELECT CRIMAS.AACPCMS AS CASE_ID
>            ,CASE
>                WHEN CRIPRI.BDCSCD = 0 THEN '19'
>                WHEN CRIPRI.BDCSCD = 1 THEN '20'
>                WHEN CRIPRI.BDCSCD = 2 THEN '21'
>                WHEN CRIPRI.BDCSCD = 9 THEN '09'
>            END AS DOCKET_CENTURY
>            ,CRIPRI.BDCSCD AS LEGACY_DOCKET_CENTURY
>            ,RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDCSYR)), 2) AS DOCKET_YEAR
>          ,CRIPRI.BDCSYR AS LEGACY_DOCKET_YEAR
>          ,CASE
>                WHEN CRIPRI.BDCSCD = 0 THEN '19' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDCSYR)), 2)
>                WHEN CRIPRI.BDCSCD = 1 THEN '20' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDCSYR)), 2)
>                WHEN CRIPRI.BDCSCD = 2 THEN '21' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDCSYR)), 2)
>                WHEN CRIPRI.BDCSCD = 9 THEN '09'
>            END AS DOCKET_FULLYEAR
>            ,RIGHT(REPEAT('000000', 6) || RTRIM(CHAR(CRIPRI.BDCSSQ)), 6) AS DOCKET_SEQUENCE
>            ,CRIPRI.BDCSSQ AS LEGACY_DOCKET_SEQUENCE
>            ,RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJNO)), 2) AS DJ_NUMBER
>            ,CRIPRI.BDDJNO AS LEGACY_DJ_NUMBER
>            ,CASE
>                WHEN CRIPRI.BDDJCT = 0 THEN '19'
>                WHEN CRIPRI.BDDJCT = 1 THEN '20'
>                WHEN CRIPRI.BDDJCT = 2 THEN '21'
>                WHEN CRIPRI.BDDJCT = 9 THEN '09'
>            END AS DJ_CENTURY
>            ,CRIPRI.BDDJCT AS LEGACY_DJ_CENTURY
>             ,RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJYR)), 2) AS DJ_YEAR
>            ,CRIPRI.BDDJYR AS LEGACY_DJ_YEAR
>            ,CASE
>                WHEN CRIPRI.BDDJCT = 0 THEN '19' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJYR)), 2)
>                WHEN CRIPRI.BDDJCT = 1 THEN '20' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJYR)), 2)
>                WHEN CRIPRI.BDDJCT = 2 THEN '21' || RIGHT(REPEAT('00', 2) || RTRIM(CHAR(CRIPRI.BDDJYR)), 2)
>                WHEN CRIPRI.BDDJCT = 9 THEN '09'
>            END AS DJ_FULLYEAR
>                FROM CRIMINAL.CCP042 CRIPRI
>                INNER JOIN CRIMINAL.CPP100 CRIMAS ON CRIMAS.AACSCD = CRIPRI.BDCSCD AND CRIMAS.AACSYR
> = CRIPRI.BDCSYR AND CRIMAS.AACSSQ = CRIPRI.BDCSSQ
>                INNER JOIN CRIMINAL.CPP170 CRIPTY ON CRIMAS.AACPCMS = CRIPTY.CPCPCMS
>                INNER JOIN CRIMINAL.CPP170 BONDPTY ON CRIMAS.AACPCMS = BONDPTY.CPCPCMS
>                WHERE (CRIPRI.BDCODE = 0173 AND CRIPRI.BDCSSQ > 0 AND CRIPRI.BDAMT > 0 AND
> CRIPTY.CPROLE = 'Defendant' AND CRIPTY.CPNAME IS NOT NULL
>                AND ((BONDPTY.CPROLE = 'Bail Payor' OR BONDPTY.CPROLE = 'Bondsman' OR BONDPTY.CPROLE =
> 'Private Surety' OR BONDPTY.CPROLE = 'Surety' ) AND CRIPRI.BDCODE = 0173 AND
> CRIPRI.BDCSSQ > 0 AND CRIPRI.BDAMT > 0))
>        );
>      SELECT * FROM SESSION.MYCASE;
>  
> Unfortunately, as soon as I run the exact same query inside of Scribe InSight, I get the following
> error:
> [42000] SQL call failed. SQL0104 - Token ; was not valid. Valid tokens: .
> Has anyone seen such a problem before?
> I can´t get this query to work in Scribe InSight no matter what I try. 
> Other queries work, like real simple ones such as select x from y. 
> Does anyone know why this query doesn´t work? This is the only type of query we run, so not
> being able to run these queries is a serious problem.
>
>  
> And the same type of problem occurs in DBArtisan.
> [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ; was not valid. Valid
> tokens: .(42000,-104)
>
>
>  
>  
> I am starting to believe this problem is more of a DB2 problem than a problem with any specific
> tool since both Idera and Scribe are coming back and saying the problem is not their software,
> and claiming since queries work but not this query, the problem is the query.
> Sadly that is not an acceptable response. I am hoping someone here encountered this before
> and can shed some light on what may be causing this problem.
>
>

Jim Aloye

RE: Problem executing multiple sql statements in same sql file
(in response to James Campbell)

I found that there is a limitation with the DBArtisan tool I am using along with the DataDirect ODBC Drivers that must be used with a separate ETL Tool the DBArtisan query must be "fed into", and unfortunately the combination of those two things DOES NOT SUPPORT creating and querying temporary table space. This came straight form the vendor. It took them several weeks, to "make that determination" but they finally got back to me and that's the issue.

 

Thanks for all your help. A lot of the suggestions here helped with other syntax issues I encountered along the way, so your responses have been greatly appreciated. Again thanks so much! 

Joe Geller

RE: Problem executing multiple sql statements in same sql file
(in response to Jim Aloye)

Jim,

Jim Tonchick suggested you just do the Select (that was inside the Insert) and not bother with the temp table.  That would be straightforward and would solve the problem.  If you were planning on several inserts (based on several selects) and that is why you were using a temp table, then the solution would be to do a Select with UNION ALL for the different parts.

Joe



In Reply to Jim Aloye:

I found that there is a limitation with the DBArtisan tool I am using along with the DataDirect ODBC Drivers that must be used with a separate ETL Tool the DBArtisan query must be "fed into", and unfortunately the combination of those two things DOES NOT SUPPORT creating and querying temporary table space. This came straight form the vendor. It took them several weeks, to "make that determination" but they finally got back to me and that's the issue.

 

Thanks for all your help. A lot of the suggestions here helped with other syntax issues I encountered along the way, so your responses have been greatly appreciated. Again thanks so much! 

Jim Aloye

RE: Problem executing multiple sql statements in same sql file
(in response to Joe Geller)

Thanks. I will try that approach the next time I encounter a similar situation.