Sequence numbers

[prasad v]

Hi

I need to create sequence numbers for my tables. this is append load.

I used Partitionnum and numpartion sys variables. this works for truncate load. when i want to do append load, it creates duplicate number. here we cannot use file or Sequence(db). But we can retrieve the max number from target table, will be performance issue. Is there any other way to achieve this?

[kiran0435]

one of the choices is:
write a routine selecting the maximum value of the ID from the Target Table. Send this value to the job as Job parameter.

and use this value along with partitionnum,numpartition and rownum variables and calculate the id.

[prasad v]

Thanks kiran,

Can you please provide code if you have, as i dont know much about routines.

[ray.wurlod]

Use a server job that gets the next available key (MAX(keycol) + 1) from the table and loads it into its user status area. Pass that as a parameter to the actual job and add it to the expression you currently have.

[kiran0435]

Here i have taken eno as ID column and Emp as table name. Arguements are DB user(pHDW_DB_User), DB Password(pHDW_DB_Password
), Database Name(pHDW_DB_Database) and Schema Name(pHDW_DB_Schema).
This is the routine which will fetch max id from the table(emp).

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H
RoutineName = 'select max id'

* Pass the parameters to the log file
Call DSLogInfo('Param pHDW_DB_User = ' :  pHDW_DB_User,RoutineName)
Call DSLogInfo('Param pHDW_DB_Database = ' :  pHDW_DB_Database,RoutineName)
Call DSLogInfo('Param pHDW_DB_Schema = ' :  pHDW_DB_Schema,RoutineName)




**  Connect to the database
LogMsg = "ERROR Connecting to the database"

Command = "db2 connect to ":pHDW_DB_Database : " USER " :pHDW_DB_User : " USING ": pHDW_DB_Password
Call DSExecute("UNIX",Command,OsOutput,OsStatus)

If OsStatus <> 0 Then
  GoTo ErrorExit
End

LogMsg = "executing the command"

SQLStatement1 = "SELECT max(eno) FROM ":pHDW_DB_Schema:".emp with ur ";

Command1 = 'db2 -x ' : '"' : SQLStatement1 : '"'

Call DSExecute("UNIX",Command1,OsOutput,OsStatus)

Count = Trim(Field(OsOutput,@FM,1))

If OsStatus = 0 Then
  If Count='-' Then
   Ans= 0
  GoTo NormalExit
  End
  Ans=Count
End

If OsStatus <> 0 Then
  GoTo ErrorExit
End



ErrorExit:
Call DSLogWarn(LogMsg,RoutineName)
Call DSLogWarn(OsOutput,RoutineName)
Call DSLogWarn(OsStatus,RoutineName)
ErrorCode = -9


NormalExit:

[prasad v]

Thanks Kiran&Ray,

Use a server job that gets the next available key (MAX(keycol) + 1) from the table and loads it into its user status area. Pass that as a parameter to the actual job and add it to the expression you ...

I want to created shared component which can be used in any of our jobs.
I feel if we go for another server job to get the next available number, if would lead to have more parameters and another job.

My approach is:

Transformer(will create one column with value'1')-->Lookup(here lookup with reference link reference is based on column value '1', it will get the Maxnumber from the table.And it brings the maxnumber to every record)--> Transformer(here we add Maxnumber with partitionnum/numpartition)

Is that good approach? Could you please advise any better method, if you have any

[ray.wurlod]

So long as you're not interested in performance. What are you looking up against?