Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Passing date dynamically to query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
kalyanp_80

New User


Joined: 03 Feb 2006
Posts: 41
Location: Hyderabad

PostPosted: Mon Jan 28, 2008 8:19 pm    Post subject: Passing date dynamically to query
Reply with quote

Hi,

I have a requirement where I need to execute query dynamically (using JCL). I am using DSNTEP2 utility to do this. However, the query which I am using will extract the data with in the date range. Start date and end date with which this query should extract data is determind by business users and as a devolper I have no control over it.

My query is, can we make this query to get execute dynamically? I mean can we use a control card where user feeds start date, end date to it and I can read it and pass it to my query? I know this is possible thru COBOL program, but I don't want to write a new COBOL program. I want to use some DB2 utilities like DSNTEP2 etc. Please help!

Thanks
Amith
Back to top
View user's profile Send private message

bbessa

New User


Joined: 03 Aug 2006
Posts: 13
Location: Brazil

PostPosted: Mon Jan 28, 2008 10:11 pm    Post subject:
Reply with quote

I know you don't want to use other languages besides JCL but I'll ask anyway: is REXX avaliable in your installation? It's quite easy and fast to do what your want with a REXX program.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Jan 28, 2008 10:38 pm    Post subject:
Reply with quote

You could write your main SQL in one control card, the user editable stuff in another 1 or 2 and the whatever else you need in other cards.
Code:

--CC 1 - yours
SELECT *
FROM table
WHERE beg_dt >= '

--CC 2 editable by users
user entered date

-CC3 - yours
' AND end_dt <= '

-CC4 - editable by users
user entered date

-CC5 yours
'
AND xxx = yyy
ORDER BY whatever you need
FOR READ ONLY;


However! Keep in mind that a user could short circuit your setup by editing their control cards like this. I don't know if it would work like this but... SQL injection is bad and users should never be able to directly edit the query parameters. You should be scrubbing their input in code and using parameterized queries if possible. They would need correct access to do this, but I assume if it is running as a production job then it has DBA type access to the tables.

[code]
--CC2
; SELECT * FROM SYSIBM.SYSTABLES;

--CC3
; DROP sometable found from beforel;
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon Jan 28, 2008 10:48 pm    Post subject:
Reply with quote

When you have a ' on one line and the value on the next you pick up the spaces inbetween.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Jan 28, 2008 10:51 pm    Post subject:
Reply with quote

I wasn't sure on that part.

But, I don't think its a good idea to let users edit a query directly that runs under a different userid that may be privileged.
Back to top
View user's profile Send private message
kalyanp_80

New User


Joined: 03 Feb 2006
Posts: 41
Location: Hyderabad

PostPosted: Tue Jan 29, 2008 9:41 am    Post subject:
Reply with quote

Thanks Steve!
Your solution does really make sense.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm
No new posts Validate the Date girishb2 DFSORT/ICETOOL 9 Tue Sep 19, 2017 1:12 am
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Julian Date to CICS ABSTTIME blayek CICS 3 Wed Aug 30, 2017 11:15 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us