[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

is there a way to pass a parameter from an AIX cmd line prompt to a DB2 query in a script

Posted on 2006-06-05
4
Medium Priority
?
336 Views
Last Modified: 2013-11-17
is there any way to make this AIX script
that runs db2 queries dynamic.

the script name is ReportStat.sh

from my AIX prompt I run this and it generates the queries
but is there anyway to make it dynamic
and not have to go into the script to change the date each time I want to run it??

can i pass a date from the prompt like
>ReportStat.sh 2006-06-05
and change the query inside to a  ' || date_input || ' for the parameter???

can this be done???

here is my static script below - any suggestions on how to make the date dynamic in the 1st query
and do the same of the reportid and stat code in the 2nd

#ReportStat.sh
db2 connect to myTestDB

db2 "SELECT count(*)"MY_FILES" FROM FILE_TBL ft, FILE_DETAIL_TBL fdt where fdt.REPORT_ID = ft.REPORT_ID and fdt.DATE > '2006-06-05'"
db2 "SELECT count(STATUS_CODE)"FILES_IN_STATUS_1000" FROM FROM FILE_TBL ft, FILE_DETAIL_TBL fdt where fdt.REPORT_ID = ft.REPORT_ID and fdt.REPORT_ID > 100 and STATUS_CODE = 1000"

db2 terminate

#EOF



is there a way to do somethign like this

db2 "SELECT count(*)"MY_FILES" FROM FILE_TBL ft, FILE_DETAIL_TBL fdt where fdt.REPORT_ID = ft.REPORT_ID and fdt.DATE >  ' || date_input || '"
then run StatReport.sh '06/02/2006' from the AIX cmd prompt??
0
Comment
Question by:annie613
  • 2
  • 2
4 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 16834711
Hi annie613,

#ReportStat.sh
db2 connect to myTestDB

db2 "SELECT count(*)"MY_FILES" FROM FILE_TBL ft, FILE_DETAIL_TBL fdt where fdt.REPORT_ID = ft.REPORT_ID and fdt.DATE > '$1'"
db2 "SELECT count(STATUS_CODE)"FILES_IN_STATUS_1000" FROM FROM FILE_TBL ft, FILE_DETAIL_TBL fdt where fdt.REPORT_ID = ft.REPORT_ID and fdt.REPORT_ID > 100 and STATUS_CODE = 1000"

db2 terminate

#EOF

ReportStat 2006-01-14
ReportStat 2006-06-05
etc...


Good Luck!
Kent
0
 

Author Comment

by:annie613
ID: 16835248
i end up with this error when I change the code and run it

SQL0180N  The syntax of the string representation of a datetime value is
incorrect.  SQLSTATE=22007
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 16835871
Hi annie613,

See what's being generated.  Right after the "db2 connect" enter this line:


echo "SELECT count(*)"MY_FILES" FROM FILE_TBL ft, FILE_DETAIL_TBL fdt where fdt.REPORT_ID = ft.REPORT_ID and fdt.DATE > '$1'"



Kent
0
 

Author Comment

by:annie613
ID: 16841148
thanks kent - once i used the echo i realized i had an extra space in the query string ' $1'  instead of '$1'

:) CHEERS!!!!!!!!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
In tuning file systems on the Solaris Operating System, changing some parameters of a file system usually destroys the data on it. For instance, changing the cache segment block size in the volume of a T3 requires that you delete the existing volu…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question