RE: Retrieve JOB (not SYSTEM) date in SQL

GENERAL style parameters worked also.


midrange-l-bounces@xxxxxxxxxxxx wrote on 06/05/2008 12:16:13 PM:

Ok Elvis,

How does this work?

How can you return a date from a CL program???

Aahh, I see DB2SQL style parameters work that way. Cool, learned
something new.

Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Thursday, June 05, 2008 1:54 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Retrieve JOB (not SYSTEM) date in SQL

I see you figured it out already, but here is what came up in case it
helps.
It has some extraneous error handling logic that you can ignore or

chuck

altogether.
Also, I opted to use DB2SQL parameter style instead of more common

GENERAL

style used by CL (old habits die hard).


CREATE FUNCTION QGPL/getJobDate()
RETURNS DATE
EXTERNAL NAME 'QGPL/GETJOBDATE'
LANGUAGE CL
PARAMETER STYLE DB2SQL
DETERMINISTIC
NO SQL
RETURNS NULL ON NULL INPUT
DBINFO
NO EXTERNAL ACTION
DISALLOW PARALLEL
NO SCRATCHPAD
NOT FENCED


/* CL code */

pgm parm(&jobDate &outind +
&sqlstate &funcname &specname &msgtext &dbinfo)

dcl &jobDate *char 10
dcl &outInd *int 2
dcl &localdate *char 6

dcl &sqlstate *char 5
dcl &funcname *char 517
dcl &specname *char 128
dcl &msgtext *char 70
dcl &dbinfo *char 816

dcl &dummy *char 2 x'0000'

monmsg (cpf0000 mch0000 sql0000) exec(goto exception)

RTVJOBA DATE(&LOCALDATE)
CVTDAT DATE(&LOCALDATE) TOVAR(&JOBDATE) TOFMT(*ISO) TOSEP(-)
chgvar &outind 0
goto exit

exception:
chgvar &outind -1
chgvar &sqlstate 'XC999'
chgvar &msgtext (&dummy *tcat 'Unexpected error occurred, +
please check the joblog')
sndpgmmsg msgid(CPF9898) msgf(QCPFMSG) +
msgdta('Unexpected exception') msgtype(*DIAG)

exit:

endpgm


HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: Retrieve JOB (not SYSTEM) date in SQL

Thanks. I had coded a TOSEP(*NONE) on my CVTDAT cmd instead of

TOSEP('-')

. It seems to work now.

CHGJOB DATE(010108)

STRSQL

SELECT getjobdate()
FROM SYSIBM/SYSDUMMY1

....+....1....
GETJOBDATE ( )
01/01/08

Is this OK or am I fooling myself.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be
confidential and privileged. If you receive this e-mail and you are
not a named addressee you are hereby notified that you are not
authorized to read, print, retain, copy or disseminate this
communication without the consent of the sender and that doing so is
prohibited and may be unlawful. Please reply to the message
immediately by informing the sender that the message was
misdirected. After replying, please delete and otherwise erase it
and any attachments from your computer system. Your assistance in
correcting this error is appreciated.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. Unless you are the addressee (or authorized to receive for the addressee), you may not use, copy or disclose to anyone this email or any information contained in this email. If you have received this email in error, please advise the sender by replying to this email, and delete this email immediately. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of Western Dental Services, Inc. Finally, the recipient should check this email and any attachments for the presence of viruses. Western Dental Services, Inc. accepts no liability for any damage caused by any virus transmitted by this email.