Dynamic Time Stamp

JDionne · Post by **JDionne** » Thu Oct 23, 2003 8:16 am

I have a process currently in SQL server that runs a query and outputs a string of date time information into a batch file. Then I run the batch file to rename a load file. I am moving away from sql server and using DataStage as my data processing tool of choice. Is there a way for data stage to dynamicaly produce a name for a file and rename it?
Jim

ray.wurlod · Post by **ray.wurlod** » Thu Oct 23, 2003 4:41 pm

There are quite a few ways, probably the easiest is to do it in code (either a before/after subroutine or a job control routine). You could also do it using a Folder stage.

To generate the date there are again many options (look in the Routines and Transforms, particularly the SDK). However, ultimately, if all you want is the system date, this is obtainable using:

the @DATE system variable (which remains constant during job execution)
the DATE() function, which always reports the date "now"

Similarly for the @TIME variable and TIME() function for the time.

Then you need to decide exactly what format you want the date in. For example, let's assume that what you have is the base name, and you want to append the date in YYMMDD and time in HHMMSS format, with underscores between. In the following code fragment, the variable DirPath contains the pathname of the directory where the file is.

Code: Select all

DirPath = "E:\MyDataFiles\"
BaseName = "TheFile"
DateTag = Oconv(Date(), "DYMD[2,2,2]" : @VM : "MCN")
TimeTag = Oconv(Time(), "MTS" : @VM : "MCN")
OldName = DirPath : BaseName
NewName = DirPath : BaseName : "_" : DateTag : "_" : TimeTag
Shell = "NT"
Command = "MOVE " : Quote(OldName) : " " : Quote(NewName)
Call DSExecute(Shell, Command, Output, ExitStatus)
If ExitStatus <> 0
Then
   Msg = "Error executing command " : Quote(Command)
   Call DSLogWarn(Msg, "Rename File")
End

Minor changes would be required if the server were on a UNIX platform.
An alternative approach would be to use DataStage BASIC's innate ability to treat a directory (folder) as a "table", in which the O/S file names are treated as record keys.

Code: Select all

DirPath = "E:\MyDataFiles\"
BaseName = "TheFile"
DateTag = Oconv(Date(), "DYMD[2,2,2]" : @VM : "MCN")
TimeTag = Oconv(Time(), "MTS" : @VM : "MCN")
OldName = BaseName
NewName = BaseName : "_" : DateTag : "_" : TimeTag
OpenPath DirPath To DirFvar
Then
   RecordLockU DirFvar, NewName
   ReadU FileContents From DirFvar, OldName
   Then
      Write FileContents To DirFvar, NewName
      Then
         Delete DirFvar, OldName
      End
      Else
         Msg = "Error renaming file."
         Call DSLogWarn(Msg, "Rename File")
      End
      Release DirFvar, NewName
      Release DirFvar, OldName
   End
End

Some error handling and handling of records locked by other process have been omitted for clarity.

kduke · Post by **kduke** » Thu Oct 23, 2003 5:56 pm

Ray

Code: Select all

RecordLockU DirFvar, NewName 
ReadU FileContents From DirFvar, OldName 
Then 
End Else
End

Should be:

ReadU FileContents From DirFvar, OldName 
Locked
End Then 
End Else
End

The RecordLockU is not necessary if you use a ReadU.

Kim.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 23, 2003 8:04 pm

The RecordLockU is on the yet-to-be-created record.
The ReadU is on the existing record.

LOCKED clauses were deliberately omitted, as noted in my post.

kduke · Post by **kduke** » Thu Oct 23, 2003 10:58 pm

Ray

Not true. A readu locks the record even if they do not exist.

Code: Select all


ED KDBP KIM
-----------------------------
   OPEN 'KDBP' TO KDBP ELSE STOP
   READU REC FROM KDBP, 'GGG' ELSE
      CMD='LIST.READU EVERY'
      EXECUTE CMD
   END
END
-----------------------------
BASIC KDBP KIM
RUN KDBP KIM

Active Group Locks:                                    Record Group Group Group
Device.... Inode..... Netnode Userno  Lmode G-Address.  Locks ...RD ...SH ...EX
1169542686 1385288425       0   3228  96 IN          1      1     0     0     0

Active Record Locks:
Device.... Inode..... Netnode Userno  Lmode     Pid Item-ID....................
1169542686 1385288425       0   3228  96 RU    3228 GGG

Kim.

JDionne · Post by **JDionne** » Fri Oct 24, 2003 7:36 am

Um...Im guessing that this proves that there are hundereds of ways to do this. Unfortunatly im not even close to being advanced enough to understand what you did. Ill share with you what I did

In the after job subroutine i put in:

Code: Select all

call \\scrbbususcnc01\Maersk_DW\WQAS\BatchFiles\wqasetl.bat #DSJobStartDate#

and I set the job type to excDos

the dos batch file is as follows

Code: Select all

rename \\SCRBBUSUSCNC01\Archive\WQAS\Changes\WQAS.txt WQAS_%1.txt
move \\SCRBBUSUSCNC01\Archive\WQAS\Changes\WQAS_%1.txt \\SCRBBUSUSCNC01\Archive

its not fancy but it does everything that I need and I can change it quickly. Ill keep studying your sugestions and as I improve in my knowlage of datastage im sure ill understand it and impliment something similar for other problems....but for know im stickin with what I know
:D
Thanx again for all the help
Jim

kduke · Post by **kduke** » Fri Oct 24, 2003 8:06 am

Jim

It is fairly simple what Ray did. I will walk you through it. You can openpath on any directory and you can treat it just like it was a hash file. Every record in this file is a complete DOS or UNIX file. So he reads in the whole OldName and writes the NewName. Next he deletes the OldName file or "record".

Code: Select all

DirPath = "E:\MyDataFiles\" 
BaseName = "TheFile" 
DateTag = Oconv(Date(), "DYMD[2,2,2]" : @VM : "MCN") 
TimeTag = Oconv(Time(), "MTS" : @VM : "MCN") 
OldName = BaseName 
NewName = BaseName : "_" : DateTag : "_" : TimeTag 
OpenPath DirPath To DirFvar 
Then 
   RecordLockU DirFvar, NewName 
   ReadU FileContents From DirFvar, OldName 
   Then 
      Write FileContents To DirFvar, NewName 
      Then 
         Delete DirFvar, OldName 
      End 
      Else 
         Msg = "Error renaming file." 
         Call DSLogWarn(Msg, "Rename File") 
      End 
      Release DirFvar, NewName 
      Release DirFvar, OldName 
   End 
End

Ray I understand why you have the recordlocku on the NewName.

Jim the way you did it is more efficient. You should be able to do your method on really large files where Ray's method will not work well on large GB plus files.

Kim.

ray.wurlod · Post by **ray.wurlod** » Sat Oct 25, 2003 1:48 am

Kim,

You missed the point. The RecordLockU statement in my code locks the new record ID, the one that I'm about to create. The ReadU statement locks the old record ID, the one that I'm about to delete. RecordLockU is to be preferred to ReadU in this case because it does not generate any I/O to determine the existence of the record.

Also, I gave an example using a MOVE command at the DOS level as my first choice of method; the OPENPATH was merely illustrating an alternative. And, dare I add, my method does not require creating a BAT file, and is more easily ported to UNIX (change MOVE to mv, Shell to "UNIX" and adjust pathname delimiters).

Regards,
Ray

kduke · Post by **kduke** » Sat Oct 25, 2003 9:23 am

Ray

I got it.

Kim.

DSXchange

Dynamic Time Stamp

Dynamic Time Stamp

RecordLockU and ReadU