How to convert time in numeric to TIME field in SQL Script?

[haneeshmarella]

Hi, I am using AS400 DB2 SQL script. I am trying to figure out how can I convert numeric time field to a TIME format. I have numeric timefield of different lengths.

Example,

5 which is 00:00:05

10 which is 00:00:10

2529 which is 00:25:29

123223 which is 12:32:23

How do I convert it into TIME within script and not the LOAD since I would like to create QVD based on script, rather than making transformations later based on QVD which takes even longer time due to the large amount of data?

Thank you.

[haneeshmarella]

With preceding load.

[haneeshmarella]

Made changes accordingly, yet takes the same time of about 1 minute to load the 500 rows.

Is it because of the speed of the DB connection? What do you think? It doesn't give me 'Optimized Load' after the reload either.

[haneeshmarella]

Made changes accordingly, yet takes the same time of about 1 minute to load the 500 rows.

Is it because of the speed of the DB connection? What do you think? It doesn't give me 'Optimized Load' after the reload either.

[sunny_talwar]

Hahahaha that is what I have been asking to test you....

1) Do just a SQL reload to see how much time it takes to reload X number of rows

2) Do the reload with preceding load to see how much time it takes to reload the same X number of rows.

To find the time it takes to do preceding load is equal to 2 - 1

Does that clarify my thought process?

[haneeshmarella]

If I get your question right,

1. I did a SQL only reload for about 500 rows, and it takes about 5-8 minutes.

2. I did preceding reload and it takes about 1 minute for the same 500 rows.

I don't understand what you mean by 'To find the time it takes to do preceding load is equal to 2 - 1'.

[sunny_talwar]

This is the most simple I can make it for you

Step 1) Reload this

SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,

    A.PHSHCN AS SHIP_TO_COUNTRY,

    A.PHSVIA AS PLANNED_SHIP_VIA,

    A.PHTCT AS TOTAL_NO_OF_CARTONS,

    B.PRUSER AS PKMS_USERS,

    B.PRCASN AS CASE#,

    B.PRTLOC AS TO_LOCATION,

    B.PRMNOP AS MENU_OPTION_NAME,

    B.PRFLOC AS FROM_LOCATION,

    B.PRSTYL AS STYLE,

    B.PRUNTS AS UNITS#,

    B.PRDCR AS DATE_STRING,

    B.PRTCR AS TIME_STRING,

    CONCAT(B.PRDCR,B.PRTCR) AS DATE_TIME_STRING,

    C.USER_NAME AS USER_NAME

FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B, CAPM01.WM0272PRDD.USUSER00 C

Step 2) Save the Log file... call it SQL_Reload_Log

Step 3)

LOAD *,

    Interval(Time#(Num(TIME_STRING, '000000'), 'hhmmss'), 'hh:mm:ss') as TIME;

SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,

    A.PHSHCN AS SHIP_TO_COUNTRY,

    A.PHSVIA AS PLANNED_SHIP_VIA,

    A.PHTCT AS TOTAL_NO_OF_CARTONS,

    B.PRUSER AS PKMS_USERS,

    B.PRCASN AS CASE#,

    B.PRTLOC AS TO_LOCATION,

    B.PRMNOP AS MENU_OPTION_NAME,

    B.PRFLOC AS FROM_LOCATION,

    B.PRSTYL AS STYLE,

    B.PRUNTS AS UNITS#,

    B.PRDCR AS DATE_STRING,

    B.PRTCR AS TIME_STRING,

    CONCAT(B.PRDCR,B.PRTCR) AS DATE_TIME_STRING,

    C.USER_NAME AS USER_NAME

FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B, CAPM01.WM0272PRDD.USUSER00 C

Step 4) Save the log file... call it Preceding_Reload_Log

Step 5) Share the log files from both the reloads here

Brother if you still are not sure of what I am trying to say, I probably wouldn't be able to make it any simpler... in that case, may be we can seek help from other experts, because I don't really know how else I can explain this concept...

[haneeshmarella]

Haha looks like I frustrated you, sorry about that. Got your points, please find the attached logs.

Fairly very new to QV, so taking time

[sunny_talwar]

I am not frustrated, but just didn't know how to explain it in a way which would help you understand what I am trying to say.... Anyways... here is what I see

1) SQL_Reload_Log

Lines Fetched - 1,538

Time Taken - 4 Seconds (Started at 2:18:09 and finished at 2:18:13

2) Preceding_Reload_Log

Fines Fetched - 1,538

Time Takes - 2 Seconds (Started at 2:18:59 and finished at 2:19:01)

So, this seems counter intuitive and might be because of other connections at the time of reloads... but it seems that preceding load is not affecting the reload time by to much.... (in this case I see that Preceding load is 2 seconds faster than simple SQL reload.

Based on this, I think you should use Preceding reload without worrying too much about the reload time going up drastically

[haneeshmarella]

Awesome, understood! Thanks a lot Sunny. Highly appreciate your patience and time taken to explain.

[sunny_talwar]

As long as we are making progress and getting to learn.... I consider this time well spent...

Happy Qliking my friend!!