running datastage job from a datastage routine

[praveenchandra]

One way to generate unique numbers is using SDKSequences hash file and its key management routines.
steps are 1. update the hash file with max value
2. open hashfile in exclusive mode
3 . read value
4. update value + 1
5. close file
Since we are loading thousands of rows, alternative was to use this
1. If @isrownum =1 then read from hash file
otherwise update the value +1
this seems to perform better, because there is no overhead of reading and writing to hash file, we are not running concurrent jobs.
What I want to do is
1. update the hash file before the job and 2 . after the job.
I have a datastage job that does the update. Other than sequencer,
how do I do this ?

thanks in advance
Praveen

[kcbland]

I don't like to use these SDK functions as they are not performant, and even the versions that allow simultaneous usage are worse. I've written my own to manage this situation.

What I can suggest to you is to add a second output link to your job at the same place where the assignment is occuring. Use a stage variable instead of a column derivation to hold the result. Then, send the result out the new output link to an aggregator with a max derivation. Output the result to the SDK function hashed file. Make sure your metadata matches the key structure and attributes of the hashed file.

Now, when the job finishes, the last used value will get output to the sequences hashed file, and the overhead is minimal.

[ray.wurlod]

Initialize a stage variable with a function that reads the hashed file. You could use KeyMgtGetNextValue etc., you could use the utility function UtilityHashLookup, you could use the intrinsic Trans() function.

Do not provide a derivation for the stage variable. This means that it retains its initial value throughout the job run.

You can update the hashed file using ExecUV as a before-stage or after-stage (or before-job or after-job) subroutine, using an appropriate UPDATE statement.

Or you could use a function such as this one.

Code: Select all

FUNCTION KeyMgtResetSequence(SequenceName, ResetValue)
$COPYRIGHT "Copyright (c) 2005-2006, Ray Wurlod.  All rights reserved.  May be used with this copyright notice intact."

      RoutineName = "KeyMgtResetSequence"

      Ans = @NULL
      GoSub ValidateArguments

      If ValidArguments
      Then

         FirstTime = @TRUE

RetryPoint:

         Open "SDKSequences" To fSequences
         On Error

            * System error attempting to open file.

            Msg = 'Error (code ' : Status() : ') attempting to open "SDKSequences" file.'
            Call DSLogFatal(Msg, RoutineName)
            Ans = 1

         End
         Then

            * SDKSequences hashed file successfully opened.  Read sequence record,
            * locking for update.

            Begin Transaction

               ReadU SequenceRecord From fSequences, SequenceName
               On Error

                  * System error attempting to read record
                  Msg = 'Error (code ' : Status() : ') attempting to read sequence "' : SequenceName : '" from "SDKSequences" file.'
                  Call DSLogFatal(Msg, RoutineName)
                  Ans = 2

               End
               Locked

                  * Attempt to set lock blocked by another process.
                  Msg = 'Sequence "' : SequenceName : '" locked by user number ' : Status() : '.  Can not be reset.'
                  Call DSLogWarn(Msg, RoutineName)
                  Ans = 3

               End
               Then

                  * Record read and locked successfully.
                  Msg = "Sequence value " : SequenceRecord<1> : " replaced with " : NewValue
                  Call DSLogInfo(Msg, RoutineName)
                  SequenceRecord<1> = NewValue

                  * Write releases the update lock.
                  Write SequenceRecord To fSequences, SequenceName
                  On Error

                     Msg = 'Error (code ' : Status() : ' updating "SDKSequences" file.'
                     Call DSTransformError(Msg, RoutineName)
                     Ans = 4
                     Rollback

                  End
                  Then

                     Ans = 0
                     Commit

                  End
                  Else

                     Msg = 'Unable to write record to "SDKSequences" file.  Status = ' : Status()
                     Call DSLogWarn(Msg, RoutineName)
                     Ans = 4
                     Rollback

                  End                    ; * end of Write statement

               End
               Else

                  Msg = "Sequence initialized to " : NewValue
                  Call DSLogInfo(Msg, RoutineName)
                  SequenceRecord<1> = NewValue

                  * Write releases the update lock.
                  Write SequenceRecord To fSequences, SequenceName
                  On Error

                     Msg = 'Error (code ' : Status() : ' updating "SDKSequences" file.'
                     Call DSTransformError(Msg, RoutineName)
                     Ans = 4
                     Rollback

                  End
                  Then

                     Ans = 0
                     Commit

                  End
                  Else

                     Msg = 'Unable to write record to "SDKSequences" file.  Status = ' : Status()
                     Call DSLogWarn(Msg, RoutineName)
                     Ans = 4
                     Rollback

                  End                    ; * end of Write statement

               End                       ; * end of ReadU statement

            End Transaction

         End
         Else

            * File does not exist, so create it then go back and try to open it.
            * But only at the first attempt.

            If FirstTime
            Then

               FirstTime = @FALSE
               Msg = 'Creating "SDKSequences" table.'
               Call DSLogInfo(Msg, RoutineName)

               Command = "CREATE TABLE SDKSequences (TYPE 18, MODULO 1, SEPARATION 4,"
               Command := " SequenceName VARCHAR NOT NULL PRIMARY KEY, "
               Command := " SequenceValue INTEGER NOT NULL DEFAULT 1) ;"
               Perform Command
               GoTo RetryPoint

            End
            Else

               Msg = 'Unable to open "SDKSequences" file. Status = ' : Status()
               Call DSLogWarn(Msg, RoutineName)
               Ans = 1

            End

         End                             ; * end of Open statement

      End


MainExit:
      
      RETURN(Ans)




ValidateArguments:

      ValidArguments = @FALSE            ; * assume the worst

      If Unassigned(SequenceName) Or IsNull(SequenceName)
      Then

         Msg = "Sequence name not supplied."
         Call DSTransformError(Msg, RoutineName)
         Ans = 5

      End
      Else

         If Unassigned(ResetValue) Or IsNull(ResetValue)
         Then

            Msg = "Reset value not supplied, so 1 used."
            Call DSLogWarn(Msg, RoutineName)
            NewValue = 1

         End
         Else

            If Num(ResetValue)
            Then

               If ResetValue Matches "1N0N" : @VM : "'-'1N0N"
               Then
                  * ResetValue is integer, use as is
                  NewValue = ResetValue
               End
               Else
                  * ResetValue is numeric, but not integer.
                  NewValue = Int(ResetValue)
                  Msg = "Non-integer reset value " : ResetValue : " replaced with " : NewValue : "."
                  Call DSLogWarn(Msg, RoutineName)
               End

            End
            Else

               * Reset value is non-numeric, so use zero.
               Msg = "Non-numeric where numeric required (reset value).  Zero used."
               Call DSTransformError(Msg, RoutineName)
               NewValue = 0

            End

            ValidArguments = @TRUE

         End

      End

      RETURN(ValidArguments)

RETURN(Ans)

[ray.wurlod]

Short Description

Reset a sequence to a given initial value

Long Description

Using the hashed file SDKSequences (which is created if necessary) this routine resets the sequence whose name is given as its first argument to the value given as its second argument.

Returns 0 if successful, otherwise returns one of the following.
1 = unable to open SDKSequences
2 = unable to read sequence record from SDKSequences
3 = unable to lock sequence record for update (other user number is reported)
4 = unable to write to SDKSequences
5 = sequence name not supplied
NULL = unknown error or invalid argument

If the second argument (reset value) is numeric but not an integer, it is truncated to the largest integer less than or equal to the supplied value.
If the second argument (reset value) is non-numeric, then the sequence value is reset to zero and a "non-numeric where numeric required" message logged.

[SharingDS]

Hi Ray,

Thanks for the Routine. It is usefull to me as well in my project.

In my project we have many sequence names (Generated using SDKSequences) and i wanted to reset all those sequences to 0 when i m moving to Dev to Test AND Test to Prod.

For that i m putting all the sequence names and the reset value into one .txt file and i m passing sequence name and Reset value to the above routine KeyMgtResetSequence in transformer stage (In a job)

When i m doing this i m getting error as ,

DataStage Job 421 Phantom 4152
The locks necessary for database operations at the current isolation
level (0) are not held by this process.
Rolling back uncommitted transactions begun within this execution environment.
Attempting to Cleanup after ABORT raised in stage CopyOfResetSeq..Transformer_2
DataStage Phantom Aborting with @ABORT.CODE = 3


I think i need to change something in the code, will you Pl suggest me what are the changes required .

Thanks

[ray.wurlod]

You can either change the ISOMODE setting in uvconfig or change the Begin Transaction statement to set a specific transaction isolation level. Refer to the DataStage BASIC manual for the BEGIN TRANSACTION or SET TRANSACTION ISOLATION LEVEL statement to learn what the options are.