DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
mjohnson62982
Participant



Joined: 01 Nov 2017
Posts: 20

Points: 170

Post Posted: Thu Jul 26, 2018 10:22 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Server
OS: Windows
I am currently trying to pass some SQL through a parameter that will allow the user to choose whether to do a full load of information from a table or to just load data from the previous day. The where clause of the select statement in the JDBC Connector looks like this:

Code:
FROM "#Library_ps.Library_Name#"."#TableName#" x
WHERE TRIDTR || '-' || MARQTR IN ('30-0', '32-1', '32-6', '41-77', '40-2', '83-0', '39-0')
AND"#SFPTR_JACKPOT_Load_Type#"
ORDER BY DHYRTR


The SFPTR_JACKPOT_Load_Type parameter set has to options: Full_Load and Partial_Load. The value of the Full_Load option is:

Code:
DHYRTR <= (select days (current date) - days (date('1900-01-01'))+1 FROM SYSIBM.SYSDUMMY1)


The Partial_Load option is:

Code:
DHYRTR <= (select days (current date) - days (date('1900-01-01'))+1 FROM SYSIBM.SYSDUMMY1)


When I try to view the data from the table I get the following error:

Quote:
The connector failed to add missing columns to the output link. The rported error is: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=ORDER;< > = <> <= !< !> != >= IN NOT, DRIVER=3.66.46


I would appreciate any help or suggestions to accomplish the task.

_________________
Respectfully,
Matt Johnson
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42677
Location: Denver, CO
Points: 219803

Post Posted: Thu Jul 26, 2018 12:15 pm Reply with quote    Back to top    

Passing SQL (or anything else for that matter) with single quotes in it as a parameter has always been an issue. This solution is a bit old but should solve your problem.

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
mjohnson62982
Participant



Joined: 01 Nov 2017
Posts: 20

Points: 170

Post Posted: Thu Jul 26, 2018 12:27 pm Reply with quote    Back to top    

I attempted this fix, however I am still getting the same message.

_________________
Respectfully,
Matt Johnson
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42677
Location: Denver, CO
Points: 219803

Post Posted: Thu Jul 26, 2018 12:39 pm Reply with quote    Back to top    

Interesting.

Do you know what the actual SQL that it ends up trying to run looks like? Trying to see if there's something more than the typical "stripping of quotes" going on here.

Oh - and just noticed that you marked this as a Server job problem, is that the case? AFAIK, that APT fix only works on PX jobs...

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
mjohnson62982
Participant



Joined: 01 Nov 2017
Posts: 20

Points: 170

Post Posted: Thu Jul 26, 2018 12:42 pm Reply with quote    Back to top    

I think it looks like this...

Code:
DHYRTR <= (select days (current date) - days (date('1900-01-01'))+1 FROM SYSIBM.SYSDUMMY1)


I wonder is there a problem with putting characters like "<=" in a string parameter?

_________________
Respectfully,
Matt Johnson
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42677
Location: Denver, CO
Points: 219803

Post Posted: Thu Jul 26, 2018 1:06 pm Reply with quote    Back to top    

I don't think so but others may know more better. So... Server job?

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
mjohnson62982
Participant



Joined: 01 Nov 2017
Posts: 20

Points: 170

Post Posted: Thu Jul 26, 2018 1:10 pm Reply with quote    Back to top    

Well it was a job transferring specified information from an iSeries table to a DB2 table. Not sure what it should have be labeled as. Sorry for any confusion.

_________________
Respectfully,
Matt Johnson
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42677
Location: Denver, CO
Points: 219803

Post Posted: Thu Jul 26, 2018 2:48 pm Reply with quote    Back to top    

I only asked because you marked the Job Type property in the initial post as Server. I'm going to assume you are using Parallel jobs as Server job questions here are (sadly) few and far between.

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42677
Location: Denver, CO
Points: 219803

Post Posted: Fri Jul 27, 2018 2:22 pm Reply with quote    Back to top    

Took another look at what you posted while I wait for something to complete and have a couple of comments. First off, your full and partial load parameter values look remarkably similar.

Secondly, perhaps try this minor change:
Code:
FROM "#Library_ps.Library_Name#"."#TableName#" x
WHERE TRIDTR || '-' || MARQTR IN ('30-0', '32-1', '32-6', '41-77', '40-2', '83-0', '39-0')
AND DHYRTR <= #SFPTR_JACKPOT_Load_Type#
ORDER BY DHYRTR

Then your parameter string would just be:
Code:
(select days (current date) - days (date('1900-01-01'))+1 FROM SYSIBM.SYSDUMMY1)

I don't see a need for the double-quotes in that part of the where clause. Be curious if that changes the error message at all.

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
mjohnson62982
Participant



Joined: 01 Nov 2017
Posts: 20

Points: 170

Post Posted: Tue Jul 31, 2018 7:46 am Reply with quote    Back to top    

I tried it without the double quotes and got the same message. But the reason I am using the parameter to give me the 2 options is that I need to allow the user to run the query to pull every row from the table or just the rows from the previous day.

_________________
Respectfully,
Matt Johnson
Rate this response:  
Not yet rated
mjohnson62982
Participant



Joined: 01 Nov 2017
Posts: 20

Points: 170

Post Posted: Tue Jul 31, 2018 7:54 am Reply with quote    Back to top    

I'm sorry the partial load SQL is:

Code:
DHYRTR = (select days (current date) - days (date('1900-01-01')) FROM SYSIBM.SYSDUMMY1)

_________________
Respectfully,
Matt Johnson
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42677
Location: Denver, CO
Points: 219803

Post Posted: Tue Jul 31, 2018 8:10 am Reply with quote    Back to top    

I understand the why of it. So did you try the modification I suggested or just without the double quotes? The latter was just an off-hand comment, the former was an attempt to get something working for you.

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
mjohnson62982
Participant



Joined: 01 Nov 2017
Posts: 20

Points: 170

Post Posted: Tue Jul 31, 2018 8:28 am Reply with quote    Back to top    

It works if it is not in a parameter.

_________________
Respectfully,
Matt Johnson
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42677
Location: Denver, CO
Points: 219803

Post Posted: Tue Jul 31, 2018 9:55 am Reply with quote    Back to top    

Sure. So... you're not going to try the minor change I mentioned? As a test.

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
mjohnson62982
Participant



Joined: 01 Nov 2017
Posts: 20

Points: 170

Post Posted: Tue Jul 31, 2018 10:13 am Reply with quote    Back to top    

The error I get now is:

Code:
The reported error is: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=SFPTR_JACKPOT_FULL_LOAD;*N.N, DRIVER=3.66.46

_________________
Respectfully,
Matt Johnson
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours