How to Handle Changing Database Name?

olgc · Post by **olgc** » Wed Oct 20, 2004 9:02 am

Hi There,

If the database naming keeps changing every day when it's read, like the db name DB_20041020, how can you handle it? For example, when DB:'_':DATE.TAG(DATE())[1,4]:DATE.TAG(DATE())[5,2]:DATE.TAG(DATE())[7,2] is used as Database in a DB stage, it doesn't convert to DB_20041020, it keeps "DB:'_':DATE.TAG(DATE())[1,4]:DATE.TAG(DATE())[5,2]:DATE.TAG(DATE())[7,2]" as the database name. So we have the follwing error:

DailyActivity..SourceServer: Attempt to access specified database failed.
SQL statement: use DB:'_':DATE.TAG(DATE())[1,4]:DATE.TAG(DATE())[5,2]:DATE.TAG(DATE())[7,2]
DailyActivity..SourceServer.DSLink1: DSP.Open GCI $DSP.Open error -2.

Any one has an idea how to hanlde the changing database name in a DB stage?

Thanks every body there,

chulett · Post by **chulett** » Wed Oct 20, 2004 9:11 am

You would need to make it a Job Parameter and then reference that parameter, bounded by pound signs (#), in the DB stage.

Do something like you are doing in an outer job control 'wrapper' or script to set the parameter value accordingly.

olgc · Post by **olgc** » Wed Oct 20, 2004 9:28 am

Hi Craig,

Thanks for your quick resopnse. I try to wrap it in a control job, but still do not know how it is handled. The code is as the following:

L$2$START:
*** Activity "JobActivity_1": Initialize job
jb$2 = "testit"
h$2 = DSAttachJob(jb$2, DSJ.ERRNONE)
If (Not(h$2)) Then
msg$ = DSMakeMsg("DSTAGE_JSG_M_0001\Error calling DSAttachJob(%1)<L>%2", jb$2:@FM:DSGetLastErrorMsg())
msg$id = "@JobActivity_1"; GoTo L$ERROR
End
p$2$1 = (ddtt)
err$code = DSSetParam(h$2, "dt", p$2$1)
If (err$code <> DSJE.NOERROR) Then
msg$ = DSMakeMsg("DSTAGE_JSG_M_0002\Error calling DSSetParam(%1), code=%2[E]", "dt":@FM:err$code)
msg$id = "@JobActivity_1"; GoTo L$ERROR
End
err$code = DSRunJob(h$2, DSJ.RUNNORMAL)
If (err$code <> DSJE.NOERROR) Then
msg$ = DSMakeMsg("DSTAGE_JSG_M_0003\Error calling DSRunJob(%1), code=%2[E]", jb$2:@FM:err$code)
msg$id = "@JobActivity_1"; GoTo L$ERROR
End
handle$list<-1> = h$2
id$list<-1> = "2"
Return

Could you show me how p$2$1 is set to fit the requirement? Suppose the db name is DB_YYYYMMDD.

Thanks,

olgc · Post by **olgc** » Wed Oct 20, 2004 1:16 pm

If p$2$1 is set as the following:

p$2$1 = 'DB':'_':DATE.TAG(DATE())[1,4]:DATE.TAG(DATE())[5,2]:DATE.TAG(DATE())[7,2]

When the job is compiled, the error below is occurred:

"Error compiling Job Control Subroutine conn. "

conn is the control job name.

Any idea?

Thanks,

vmcburney · Post by **vmcburney** » Wed Oct 20, 2004 5:28 pm

This looks like Sequence job code that has been copied and pasted into a batch control job. The problem with this code is that it's not really maintainable as it's generated when the sequence job is compiled and is not meant to be modified by users.

I'd try turning this back into a sequence job and see if you can add the required functionality via a combination of a sequence job and called job control routines.

alokkum · Post by **alokkum** » Thu Oct 21, 2004 7:20 pm

You can make this happen through the sequencer job control. Create a parameter file which contains the date to be tagged to the table/database names. Have this parameter in the job as well as in the sequencer.
Do proper parameter mappings in the sequencer for any such job which has a changing table/database name.
After compiling the sequencer, take the compiled code from the job control and create another batch job where you paste this code.
Make necessary additions to read these parameter variables from the parameter file.
Hope this works
Thanks

kduke · Post by **kduke** » Thu Oct 21, 2004 8:06 pm

I think this is not a good idea. Sequencer code is very very ugly. To create this manual process to cut and paste this code into a batch job is a waste of time. I posted a routine the can be called from a routine activity which will set any job parameter in the current sequence. This is super clean. All you have to do is modify this routine to lookup any parameter value you like and then run the job that needs this value after this routine. It is exactly what is missing from a sequence. NO MORE BATCH JOBS EVER. I can write BASIC code better than most of you and this is by far the cleanest way to lookup parameters and feed them into a sequence.

Do a search for KgdSetLastKeyParam. If you need help modifying it then let me know. I have another version which will read a whole list of parameters from a sequential file and set any or all of them. Peter Lai wrote it. He has used Universe since 1986 and DataStage since version 1.0. He is the best.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 21, 2004 11:15 pm

Ooh, that sounds like a challenge! I come from UniVerse's parent (Prime INFORMATION), beginning in 1983, via UniVerse, and DataStage 1.0 also.

chulett · Post by **chulett** » Thu Oct 21, 2004 11:33 pm

Oh, well... I've worked with Prime systems in the distant past, and DataStage since 3.1. Close but no cigar.

kduke · Post by **kduke** » Thu Oct 21, 2004 11:48 pm

Ray

I said most of the posters not all. I only started using Universe in 1986 and Pick before that. Still I think that is long enough to recognize a better way to do something.