topic Re: DB2 SQL from Shell Script in Operating System - HP-UX

DB2 SQL from Shell Script

Prabhu_9 — Wed, 26 Sep 2007 03:34:24 GMT

hi,
I want to run a DB2 script from the shell.
How can i do it?
I even want to accept an argument value from the user which i'l be substituting in the script & then execute the DB2 script by a shell script.

Please help me out...
tx in advance.

Re: DB2 SQL from Shell Script

Steven E. Protter — Wed, 26 Sep 2007 03:52:37 GMT

Shalom,

General scripting rules will work here. Simplistic example.

arg1=$2
# second argument is in a variable, which can be used any way you like below

sqlplus -s ${DB_CONNECT} < INSERT INTO T_PROCESS_STATS ( START_DT, PROCESS_NAME_TXT, END_DT, RECORDS_PROCESSED_VAL, MSG_TXT)
VALUES (
SYSDATE, 'backup_ref_data_dly.ksh', SYSDATE, 0, 'Backup of reference data - daily');
SHOW SQLCODE
end_sql

Note the general principle is to put what would normally be hand typed statements into the script line by line. Each line simulates an enter between <
Change the sqlplus to the proper DB2 equivalent and you can script darned near anything you want.

SEP

Re: DB2 SQL from Shell Script

Prabhu_9 — Wed, 26 Sep 2007 06:05:53 GMT

Is this for me ?

Re: DB2 SQL from Shell Script

OldSchool — Wed, 26 Sep 2007 10:46:41 GMT

"Is this for me ?"

Yes.

He's showing you a shell script containing a "here-doc". That is the part from
bounded by "<
anything between those strings are sent, in this case, to the "sqlpus" command as if they were typed at the keyboard.

Note that their are rules about here-docs. from the sh-posix man page:

<<[-]word The shell input is read up to a line that matches word, or to an end-of-file. No parameter substitution, command substitution or file name generation is performed on word. The resulting document, called a here-document, becomes the standard input. If any character of word is quoted, no interpretation is placed upon the characters of the document. Otherwise, parameter and command substitution occurs, \newline is ignored, and \ must be used to quote the characters \, $, `, and the first character of word. If - is appended to <<, all leading tabs are stripped from word and from the document.

Re: DB2 SQL from Shell Script

Prabhu_9 — Wed, 26 Sep 2007 22:57:39 GMT

*** Replace to DB2 equivalent*****
i have samples for calling Oracle or SQL Server SQLs from unix shell script... but
iam not finding script to call DB2 from shell script. thats what i wanted.

Re: DB2 SQL from Shell Script

OldSchool — Thu, 27 Sep 2007 10:56:11 GMT

hmm...db2 command line processor is called "db2"...is that what you're looking for?

also see here:

http://sqlrelay.sourceforge.net/sqlrelay/gettingstarted/db2.html

Re: DB2 SQL from Shell Script

Prabhu_9 — Thu, 27 Sep 2007 23:01:20 GMT

Nope.

Iam looking for samplee shell script which 1) connects to DB2
2) Fetches count from a table.

I just need exact syntax of how to connect to DB2 from shell script and fetch the count.

Re: DB2 SQL from Shell Script

OldSchool — Fri, 28 Sep 2007 08:56:36 GMT

well, how would you do it from the command line? If you can't answer that, it's time to hit the manuals