Link to home
Start Free TrialLog in
Avatar of astorer
astorer

asked on

Stored Procedure job date when invoked from WebSphere

We use WebSphere Application Server 5.1 and access data in DB2/400 over JDBC using the toolbox driver in jt400.
There are instances where we call stored procedures (RPG) in DB2/400.  These stored procedures make use of Job Date in their processing.  The SQL and stored procedures run in the QZDASOINIT job.  

Ok the problem is that this job date is the date the job was started, so as we pass midnight, the job date is incorrect.  So, we can end those jobs at midnight and new ones will be started automatically as WebSphere needs them (they are prestart jobs).

Unfortunately, the job date of the new jobs is not the date that QZDASOINIT is started, it is the date that WebSphere is started.  It "inherits" the start date from WebSphere.  Note also that WebSphere and/or OS/400 take care of starting extra QZDASOINIT jobs as required - I cannot manage the starting of these jobs myself.

So, to get the right job date, we must
Before midnight
 - stop the QZDASOINIT jobs
 - stop WebSphere
After midnight
 - start WebSphere
 - When WebSphere  tries to use JDBC, QZDASOINIT jobs are started with the correct job date

Is there an alternative solution here that means I do not need to restart WebSphere as I want to run 24x7 ?

Thanks
Andrew
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

would it be easier to have a table with "your system date" on it...

then all your processes use it... and you just need 1 job to advance the date automatically...
hi

do you need a specific date, or just the date of the time your run the job ?
why not using current date ?
Avatar of astorer
astorer

ASKER

Thanks for the comments.  There is a significant amount of existing software there that uses job date.
Much of this has been "wrapped" as stored procedures.  To change all of this software is not really an option for us at this stage.
So we have to use job date.

Andrew
Hi
are you using
ENDPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT) OPTION(*IMMED)
STRPJ SBS(QSYSWRK) PGM(QSYS/QZDASOINIT)
to start and stop the jobs?

Dave
Avatar of astorer

ASKER

No we use:
ENDSBS     SBS(QUSRWRK) OPTION(*IMMED)
STRSBS     SBSD(QUSRWRK)

Andrew
Andrew:

AFAIK, the answer is not to use job date. Use system date instead. If it's an RPG program, it was coded inappropriately.

A date field may be initialize to the _system_ date by specifying INZ(*SYS) instead of INZ(*JOB). System date can also be retrieved by executing the TIME operation code. Those are generally the easiest ways of getting the current date from the system clock.

Tom
Hey, Andrew.

If you _have_ to use job date, try retrieving the current date and changing the job date. This would be a small change to your software.
The CL commands would look like
RTVSYSVAL SYSVAL(QDATE) RTNVAR(&TODAY)
CHGJOB JOB(*) DATE(&TODAY)  
You can do the equivalent in RPG if required, using TIME to get the system date and QCMDEXEC to execute the CHGJOB, for example.
If you use this approach, you may want to change the job date back at the end of the procedure.
I have not tested this, but I think it should work.

Barry
Avatar of astorer

ASKER

Thanks for the comments.
1. We have a lot of existing software that uses Job Date.  That's because this is software that was developed using Synon (and generating RPG) a long time ago and Job Date was all that you could use in that version of Synon.
2. There is so much software that the retesting alone would be prohibitively expensive
So we are stuck with using Job Date.

Unfortunately Barry, this suggestion you make would not work.  
 - as these are stored procedures, there is no CL before the RPG starts
 - as above, there is too much RPG to change to use the Job Date
 - the QZDASOINIT jobs can stop after so much use and new jobs start as required.  These new jobs have the same job date as WebSphere.

We have been a number of experts trying to solve this problem but each suggestion hits a brick wall.  I'd hoped that someone here may have had this same problem and identified a solution one way or another.

thanks again
Andrew
ASKER CERTIFIED SOLUTION
Avatar of Barry Harper
Barry Harper
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of astorer

ASKER

Thanks for the suggestion.  This looks very interesting - I'll check this out.

thanks again
Andrew