QMF - System Time Conversion 2

[ks01]

Hello ...

I hope I am posting this in the correct forum. I searched for QMF but was unable to locate anything.

Our shop runs DB2 V7.1.0 and QMF V7R2 on OS/390. I have created a stored procedure that queries several tables and displays the result set using a form. Included in the header of the form are the &DATE and &TIME variables.

The system time on our mainframes is set to GMT due to having data centers scattered around the globe. All of the folks that will use the stored procedure are located in the Pacific time zone. Is there a way to have the &DATE and &TIME variable show the date and time as it is in PT/PST instead of GMT?

Thanks.

 

[MarcLodge]

As far as I am aware the QMF &date and &time variables are defined at installation level and are not formattable locally.

What I would suggest is to create the date/time in the format that you require using SQL. Do not display this column in your report by typing 'omit' in the usage column on the form, but then use the column number in the report heading.

Hope this helps

Marc

 

[ks01]

Marc ...

Hi!

Thank you for the information.

Do you know of a way to subtract Pacific Time from GMT? I assume using a function like CURRENT TIMESTAMP - X in the SELECT statement, but I'm not sure of what X would be since sometimes we're 8 hours behind GMT and sometimes 7.

Any ideas?

Thanks in advance ...

Kent

 

[MarcLodge]

Kent,
As I understand it, the mainframe is running at GMT, but your users wish to see the report date/time in Pacific Time.

As far as I am aware, there is no way to do this from within QMF. Pacific Time is sometimes 7 hours, other times 8 hours, behind GMT and I think that you may have to feed the difference in as a variable.

Do you have the time difference available on a file outside of QMF, that can be given to the sotered procedure, or is there a DB2 table with this information on?

I'm thinking that you are going to have to have a manually maintained variable outside of the stored procedure, or available on a table.

Is your stored procedure a QMF procedure, or a 'proper' stored procedure, by the way?

Marc

 

[ks01]

Marc ...

I don't have the time difference in any kind of data set outside of QMF. I not familiar with setting something like that up.

Here is the setup of the proc:

[tt]RUN RCX1.SETGLBL
IMPORT QUERY FROM 'PCX.SQL.LIB(QSTA406)'
RUN QUERY (F RCX1.FSTA406
RUN RCX1.PPRINT19[/tt]

If I need to create something other than what I have, do you have any suggestions?

Thanks again ...

Kent

 

[MarcLodge]

Kent,
OK, so it's a QMF proc rather than a DB2 stored procedure. I would suggest that in your SETGLBL proc, you set up a variable that contains the time difference, and use this in your query. Unfortunately, I can't think of a way to automatically trigger this to be updated when Pacific Time changes, so it may mean that you will have to update this manually.

Are you running this procedure in batch or is it run online? Is there any local variable that gives Pacific Time (or the difference between PT and GMT) that can be passed to QMF?

Sorry to be not much help, but I can't think of a way of obtaining different time zones on a mainframe, apart from setting up a reference table with the start date, end date, and time zone difference for the next 10 years. But I think this might be a little over the top.

Marc

 

[ks01]

Marc ...

Thank you for the follow-up help.

I did a Google search on subtracting hours from a timestamp in DB2 and I tried using the variable [tt]CURRENT TIME - CURRENT TIMEZONE[/tt], but I got back the value of [tt]0[/tt]. I think maybe the example I found on Google may have been for DB2 on some other platform.

I looked in the DB2 UDB for OS/390 SQL Reference guide and didn't see any time zone support either. Here is my current [tt]SETGLGL[/tt] variable:

[tt]PROC RCX1.SETGLBL LINE 1

SET PROFILE (CA=UPPER,CO=YES,D=PERIOD,LA=SQL,LE=60,P=' ',W=132)
SAVE PROFILE
SET GLOBAL (A=(') PERCENT_SIGN=(%)
*** END ***[/tt]

Could you let me know what I would need to do to implement your suggestion as to adding a global variable that would define the difference? I don't mind going in an changing the PROC twice a year when times change.

Thanks again ...

Kent

 

[MarcLodge]

Kent,
What you need to do is to change the proc to:

SET PROFILE (CA=UPPER,CO=YES,D=PERIOD,LA=SQL,LE=60,P=' ',W=132)
SAVE PROFILE
SET GLOBAL (A=(') PERCENT_SIGN=(%)TIME_DIFF=8

or whatever the time difference is.

In your query, you want to add two columns:

SELECT DATE(CURRENT_TIMESTAMP - &TIME_DIFF HOURS)
,TIME(CURRENT_TIMESTAMP - &TIME_DIFF HOURS)


In your FORM.MAIN type OMIT (or just O) under the usage column for these columns (probably defined as COL1 and COL2 if the first columns in the SELECT)

In your FORM.PAGE type &1 and &2 whereever you wish to see the fields appear.

Hope this helps.

Marc

 

[stevexff]

Kent

The CURRENT TIMEZONE won't help you, as if your server uses GMT/UTC then the offset will always be zero. Marc's solution is neater, as it effectively makes use of the client's offset to UTC to give the right result.

The only thing I'd do to make it easier to maintain is to add another global SUMMER_TIME, which has a value of 0 or 1 (for no and yes), and change the select to

SELECT DATE(CURRENT_TIMESTAMP - (&TIME_DIFF + &SUMMER_TIME) HOURS)
      ,TIME(CURRENT_TIMESTAMP - (&TIME_DIFF + &SUMMER_TIME) HOURS)

Then you don't have to think in terms of offsets, just a simple 'is it summer time or not?' when you come to maintain it later.

 

[ks01]

Thanks Marc and Steve, the information you provided has been very helpful!

Kent