*TIMX display format

, ,
1

Hi,

While trying to move records from Natural/Adabas file to DB2 in Mainframe, we saw for some of fields declared as datatype T(TIME) it is in packed format.

Eg:
L DB Name F Leng S D
2 BC #CHANGED T 0012 N D = > in NSD file

In the ADAREP report for the same, it is looking as below

Level I Name I Length I Format I Options I Parent of I
2 I BC I 7 I P I DE,NU I SUPERDE I

In NATURAL program,

#CHANGED = *TIMX => Statement used to fill value for #CHANGED

And in the Mainframe file it is looking as HEX’0631390886378F’

Can anyone help us in getting to know the understandable value for this #CHANGED field?

Regards,
Vinodh

2

When using Natural to transfer data then simply apply an edit mask to get the readable format you need.

3

Hi Wolfgang,

Thanks for your reply!

We have been given a extracted file from Natural/adabas and currently in our mainframe we do not have Natural to do edit mask. So we just wanted to understand how this internal time format is calculated and stored.

Regards,
Vinodh

4

T (Time) internal format basically contains the date and time .

It is the number of tenths of seconds since day 0 year 0.

Consider the following program and output.

DEFINE DATA LOCAL
1 #TIME (T)
1 REDEFINE #TIME
2 #TIMEB (B7)
1 #TIMEC (P15)
END-DEFINE
*
INCLUDE AASETC
WRITE 5T ‘NULL TIME VARIABLE ==>’ #TIME 5X ‘IN BINARY==>’ #TIMEB /
*
MOVE *TIMX TO #TIME
*
WRITE 5T ‘WITH *TIMX IN #TIME ==>’ #TIME 5X ‘IN BINARY==>’ #TIMEB /
*
COMPUTE #TIMEC = ( 8 * 60 * 60 * 10)
+ ( 10 * 60 * 10 )
+ ( 2014 * 365 * 24 * 60 * 60 * 10 )
+ ( 250 * 24 * 60 * 60 * 10)
WRITE ‘COMPUTED TIME 2014 ==>’ #TIMEC
END

Page 1 14-09-12 08:20:52

NULL TIME VARIABLE ==> 00:00:00     IN BINARY==> 0000000000000C

WITH *TIMX IN #TIME ==> 08:20:52     IN BINARY==> 0635776428524C

COMPUTED TIME 2014 ==> 635351334000

You will note that “COMPUTED TIME 2014” is not the same as the second line “WITH *TIMX IN #TIME”.

This is because I did not write out all the details for Computed Time. You would have to take into account leap years, the exact number of days in 2014 thus far (I approximated 250), etc

If you go through the exact computation, it should equal the binary number in #TIME.

1 Like
5

Hi Steve,

Thanks for your reply!
It helped me a lott in understanding the binary value in *TIMX field.
Now, here comes my requirement i have unloaded ADABAS data files in flat file format which has been FTPed to Windows location.

Will we be able to use this *TIMX field (Packed field, 7 bytes) by unpacking into 13 digit value as 0635776428524 and load as TIMESTAMP in Mainframe DB2?

I do not have NATURAL installed in my machine, so will not be able to use the EDIT MASK on this field from Natural.

Is writing a macro(to make it a readable timestamp) be only solution or we have options to use this field(unpacked 13 bytes) directly for loading in Mainframe DB2?

Kindly suggest.

Regards,
Vinodh

6

no

7

Writing a macro to convert Natural’s internal time (*TIMX) to whatever date format you require would seem to be the best way for you to proceed.

After writing the macro, you could post it here for anyone who encounters such an impasse. There will probably not be too many such shops since this is mainly an Adabas/Natural forum.

Is this a “one time” requirement? That is, you have old Adabas files that you must extract date/time values from, and store in a DB2 file. Or, is this an ongoing requirement for new data. If the latter, there must be Natural code somewhere. So, you should convert the data there, using edit masks, before storing on Adabas files.

8

Having no COBOL compiler, I created a Natural program to demonstrate the logic to convert a *TIMX value. I kept the code as COBOL-like as I could. (This was very painful for a Natural purist.)

DEFINE DATA LOCAL                 /* T2ALPHA
1 #T (T)                INIT <E'09/19/2014 11:12:13'>
                        1 REDEFINE #T
  2 #PACKED (P13)
1 #MTHS
  2 #JAN (I4)           INIT <31>
  2 #FEB (I4)           INIT <28>
  2 #MAR (I4)           INIT <31>
  2 #APR (I4)           INIT <30>
  2 #MAY (I4)           INIT <31>
  2 #JUN (I4)           INIT <30>
  2 #JUL (I4)           INIT <31>
  2 #AUG (I4)           INIT <31>
  2 #SEP (I4)           INIT <30>
  2 #OCT (I4)           INIT <31>
  2 #NOV (I4)           INIT <30>
  2 #DEC (I4)           INIT <31>
                        1 REDEFINE #MTHS
  2 #MTH (I4/12)
1 #COMPUTE
  2 #YEARS   (P13)
  2 #DAYS    (P13)
  2 #HOURS   (P13)
  2 #MINUTES (P13)
  2 #SECONDS (P13)
  2 #TENTHS  (P13)
1 #OUT
  2 #YY (N4)
  2 #MM (N2)
  2 #DD (N2)
  2 #HH (N2)
  2 #II (N2)
  2 #SS (N2)
  1 #TT (N1)
1 #I (P13)
END-DEFINE
FORMAT SG=F
*
IF  #PACKED < 499230432000
 OR #PACKED > 852037055990
  THEN
    WRITE 'Packed date is out of range (499230432000 - 852037055990)' #PACKED
    STOP
END-IF
DIVIDE 10 INTO #PACKED  GIVING #SECONDS REMAINDER #TENTHS
DIVIDE 60 INTO #SECONDS GIVING #MINUTES REMAINDER #SECONDS
DIVIDE 60 INTO #MINUTES GIVING #HOURS   REMAINDER #MINUTES
DIVIDE 24 INTO #HOURS   GIVING #DAYS    REMAINDER #HOURS
*
ADD 2 TO #DAYS
MOVE -1 TO #YEARS
FOR #I = #DAYS 366 -365
  ADD 1 TO #YEARS
  IF        #YEARS = #YEARS /   4 *   4
   AND (NOT #YEARS = #YEARS / 100 * 100
         OR #YEARS = #YEARS / 400 * 400)
    THEN
      SUBTRACT 1 FROM #I
  END-IF
END-FOR
MOVE #I TO #DAYS
IF  #DAYS > 0
  THEN
    ADD 1 TO #YEARS
END-IF
*
IF   #YEARS  = #YEARS / 400 * 400
 OR  #YEARS  = #YEARS / 4   * 4
 AND #YEARS <> #YEARS / 100 * 100
  THEN
    ADD 1 TO #FEB                      /* leap year
END-IF
*
IF  #DAYS = 0                          /* compute month - method 1
  THEN
    MOVE 12 TO #MM
    MOVE 31 TO #DAYS
  ELSE
    IF  #DAYS <= #JAN
      THEN
        MOVE 01 TO #MM
      ELSE
        SUBTRACT #JAN FROM #DAYS
        IF  #DAYS <= #FEB
          THEN
            MOVE 02 TO #MM
          ELSE
            SUBTRACT #FEB FROM #DAYS
            IF  #DAYS <= #MAR
             THEN
               MOVE 03 TO #MM
              ELSE
                SUBTRACT #MAR FROM #DAYS
                IF  #DAYS <= #APR
                  THEN
                    MOVE 04 TO #MM
                  ELSE
                   SUBTRACT #APR FROM #DAYS
                    IF  #DAYS <= #MAY
                      THEN
                        MOVE 05 TO #MM
                      ELSE
                        SUBTRACT #MAY FROM #DAYS
                        IF  #DAYS <= #JUN
                          THEN
                            MOVE 06 TO #MM
                          ELSE
                            SUBTRACT #JUN FROM #DAYS
                            IF  #DAYS <= #JUL
                              THEN
                                MOVE 07 TO #MM
                              ELSE
                                SUBTRACT #JUL FROM #DAYS
                                IF  #DAYS <= #AUG
                                  THEN
                                    MOVE 08 TO #MM
                                  ELSE
                                    SUBTRACT #AUG FROM #DAYS
                                    IF  #DAYS <= #SEP
                                      THEN
                                        MOVE 09 TO #MM
                                      ELSE
                                        SUBTRACT #SEP FROM #DAYS
                                        IF  #DAYS <= #OCT
                                          THEN
                                            MOVE 10 TO #MM
                                          ELSE
                                            SUBTRACT #OCT FROM #DAYS
                                            IF  #DAYS <= #NOV
                                              THEN
                                                MOVE 11 TO #MM
                                              ELSE
                                                SUBTRACT #NOV FROM #DAYS
                                                MOVE 12 TO #MM
                                            END-IF
                                        END-IF
                                    END-IF
                                END-IF
                            END-IF
                        END-IF
                    END-IF
                END-IF
            END-IF
        END-IF
    END-IF
END-IF
*
* IF  #DAYS = 0                          /* compute month - method 2
*   THEN
*     MOVE 12 TO #MM
*     MOVE 31 TO #DAYS
*   ELSE
*     FOR #I = 1 12
*       IF  #DAYS <= #MTH (#I)
*         THEN
*           MOVE #I TO #MM
*           ESCAPE BOTTOM
*       END-IF
*       SUBTRACT #MTH (#I) FROM #DAYS
*     END-FOR
* END-IF
*
MOVE #YEARS   TO #YY         /*  pretty print
MOVE #DAYS    TO #DD
MOVE #HOURS   TO #HH
MOVE #MINUTES TO #II
MOVE #SECONDS TO #SS
MOVE #TENTHS  TO #TT
WRITE ' Natural:' #T (EM=YYYY/MM/DD' 'HH:II:SS.T)
WRITE ' Numeric:' #PACKED
WRITE 'Computed:' #YY '/' #MM '/' #DD
    / '         ' #HH ':' #II ':' #SS '.' #TT
END

My example uses 635782579330, which translates to 2014/09/19 11:12:13.0.

* Page     1                                                   09/19/14  00:01:36
  
  Natural: 2014/09/19 11:12:13.0
  Numeric:  635782579330
 Computed: 2014 /  9 / 19
           11 : 12 : 13 . 0
9

Hi,
I have created a macro to convert TIMX value. It converts values accurate to minutes, there will be some variance in seconds value.

Open a new Microsoft Excel workbook, place a button named “ConvertTimestamp” on sheet1, open the macro editor and paste the below code and save the workbook. Now place the values you want to convert on sheet2 first column and click button “ConvertTimestamp” to convert.

"Private Sub ConvertTimestamp_Click()

Dim nattime As Double
Dim contime As String
Dim avgdays_peryear As Double
Dim Tenthofsec_year As Double
Dim no_of_leapyear As Double
Dim no_normal_year As Double
Dim tot_no_days As Double
Dim tot_tenthofsec As Double
Dim rem_tenthofsec As Double
Dim temp_hold_tenthofsec As Double

Dim cal_year As Integer
Dim cal_days As Integer
Dim cal_Hours As Integer
Dim cal_min As Integer
Dim cal_sec As Integer
Dim cal_date As String
Dim i As Long
avgdays_peryear = 365.241807353158
Tenthofsec_year = 365.241807353158 * 24 * 60 * 60 * 10

i = 1

nattime = Sheet2.Cells(i, 1)
Do Until nattime = 0
cal_year = WorksheetFunction.Quotient(nattime, Tenthofsec_year)
no_of_leapyear = Int((cal_year / 4) - (cal_year / 100) + (cal_year / 400) - 1)

If ((cal_year Mod 4) = 0 And (cal_year Mod 100) <> 0) Or ((cal_year Mod 4) = 0 And (cal_year Mod 100) = 0 And (cal_year Mod 400) = 0) Then

no_of_leapyear = no_of_leapyear - 1

End If

no_normal_year = cal_year - no_of_leapyear
tot_tenthofsec = ((no_normal_year * 365) + (no_of_leapyear * 366)) * (864000)
rem_tenthofsec = nattime - tot_tenthofsec
temp_hold_tenthofsec = rem_tenthofsec / 864000
cal_days = Int(rem_tenthofsec / 864000)

temp_hold_tenthofsec = temp_hold_tenthofsec - cal_days
cal_Hours = Int(temp_hold_tenthofsec * 24)

rem_tenthofsec = (temp_hold_tenthofsec * 24) - cal_Hours
cal_min = Int(rem_tenthofsec * 60)

temp_hold_tenthofsec = (rem_tenthofsec * 60) - cal_min
cal_sec = Int(temp_hold_tenthofsec * 60)

cal_date = Format(DateSerial(cal_year, 1, cal_days), “YYYY-MM-DD”)
contime = cal_date & “-” & Format(cal_Hours, “0#”) & “.” & Format(cal_min, “0#”) & “.” & Format(cal_sec, “0#”) & “.000000”
Sheet2.Cells(i, 1) = contime
Sheet1.Cells(i, 1) = Int(nattime)
Sheet1.Cells(i, 2) = contime

i = i + 1
nattime = Sheet2.Cells(i, 1)
Loop

MsgBox “Conversion Completed”

End Sub "

PS: Not tested completely. Testing with multiple TIMX values of different years required before production use. To ensure accuracy.