|
View previous topic :: View next topic
|
| Author |
Message |
kalyanp_80
New User
Joined: 03 Feb 2006
Posts: 41
Location: Hyderabad
|
|
|
|
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 |
|
 |
bbessa
New User
Joined: 03 Aug 2006
Posts: 13
Location: Brazil
|
|
|
|
| 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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| When you have a ' on one line and the value on the next you pick up the spaces inbetween. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
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 |
|
|
kalyanp_80
New User
Joined: 03 Feb 2006
Posts: 41
Location: Hyderabad
|
|
|
|
Thanks Steve!
Your solution does really make sense. |
|
| Back to top |
|
|
|
|
