KeyMgtGetNextValueConcurrent() generating duplicate records.

sreddy666 · Post by **sreddy666** » Wed Jun 13, 2012 10:13 am

Hi Friends,

i know this issue is already discussed in this forum.. but i need some guidence on this..

I have server job which is using KeyMgtGetNextValueConcurrent() routine..

1> as part of upgrade jobs/routines/shared contianers.. moved from 7.5.2 to 8.5 and re-compiled.

2> all tables were moved to new DB server.

Issue : now when i ran the job which is having KeyMgtGetNextValueConcurrent() is generating the number which is already existing in the table and row is getting dropped.

So if i want my job to use max value of the table ... how to proceed.

Routine:

Code: Select all

 EQUATE RoutineName TO 'KeyMgtGetNextValueConcurrent'

    If NOT(Initialized) Then
	* Not initialised. Attempt to open the file.
       Open "SDKSequences" TO SeqFile Else
            * Open failed. Create the sequence file.
            EXECUTE "CREATE.FILE SDKSequences DYNAMIC"
            Open "SDKSequences" TO SeqFile Else
                Ans = -1
                Goto EndRtn
            End
       End
       
       Initialized = 1
       KeyName = ""
       NextVal = ""
    End

    * Read the named record from the file.
    * This obtains the lock (waiting if necessary).
    Readu StartVal From SeqFile, Arg1 Else
         StartVal = 1
    End
    
    Locate Arg1 In KeyName By 'AL' Setting Idx Else
        Ins Arg1 Before KeyName<Idx>
        Ins StartVal Before NextVal<Idx>
    End

    Ans = StartVal

    * Increment the sequence value, and write back to file.	
    * This releases the lock.
    NextVal<Idx> = StartVal + 1
    Write NextVal<Idx> On SeqFile, Arg1 Else Ans = -1

EndRtn:

can i use below cmd

Code: Select all

UPDATE SDKSequences USING DICT VOC SET F1 = '1' WHERE @ID = 'Your_Sequence_Name';

where i have to execute the above cmd?
i.e IBM\InformationServer\Server\DSEngine.. or any other location..

thanks in advance

chulett · Post by **chulett** » Wed Jun 13, 2012 10:21 am

You should have migrated the SDKSequences hashed file from the old project to the new one so the sequence values were retained, sounds like you can still do that now. That or you need to connect to each project on the new server that leverages that routine and use the update code you posted to reset the value for each sequence to the current high-water value of the corresponding table (not 1).

sreddy666 · Post by **sreddy666** » Wed Jun 13, 2012 10:28 am

Hi Criag,

I can't see the full content of your post..

can i copy the SDKSequence hash file from old project to new project.. now? becuase already SDKSequence hash file is created in new project.

chulett · Post by **chulett** » Wed Jun 13, 2012 10:38 am

sreddy666 · Post by **sreddy666** » Wed Jun 13, 2012 10:49 am

Hi Craig,

i am able to see the SDKSequence hash file but i am not able to see the hidden file .type30

Can i copy(right click copy & paste) paste only SDKSequence hash file .

chulett · Post by **chulett** » Wed Jun 13, 2012 11:27 am

Did you edit the routine code?

From what I recall, it creates a Type2 hashed file and yet the posted code says 'DYNAMIC' as in Type30. Does it match with what you see for that same routine back on your 7.x server?

sreddy666 · Post by **sreddy666** » Wed Jun 13, 2012 11:55 am

Hi Craig,

I didn't modified the routine...

Yes .. it doesn't match with routine what was on 7.5.2

routine on 7.5.2
---------------
EQUATE RoutineName TO 'KeyMgtGetNextValueConcurrent'

If NOT(Initialized) Then
* Not initialised. Attempt to open the file.
Initialized = 1
Open "SDKSequences" TO SeqFile Else
* Open failed. Create the sequence file.
EXECUTE "CREATE.FILE SDKSequences 2 1 1"
Open "SDKSequences" TO SeqFile Else Ans = -1
End
End

* Read the named record from the file.
* This obtains the lock (waiting if necessary).
Readu NextVal From SeqFile, Arg1 Else
NextVal = 1
End

Ans = NextVal
NextVal = NextVal + 1

* Increment the sequence value, and write back to file.
* This releases the lock.
Write NextVal On SeqFile, Arg1 Else Ans = -1
-----------

But i have copied SDKSequence from old project to new test env project and ran few jobs..
The jobs which was finished with warnings(row dropped due to duplicate value) , now finished without any warnings.

So can i copy same file to PROD env and can run my jobs?

chulett · Post by **chulett** » Wed Jun 13, 2012 1:13 pm

I would imagine so if those are the appropriate values to use in Prod. Otherwise you can set them using the code you posted, modified properly of course.

sreddy666 · Post by **sreddy666** » Thu Jun 14, 2012 4:01 am

Hi craig,

i have moved SDKSequence hash file to PROD env and most of the jobs finished without any duplicate rows only 2 jobs were dropping records as generated value is already existing in table.

So now i am planning to update only that 2 sequenc using below cmd.

UPDATE SDKSequences USING DICT VOC SET F1 = '1' WHERE @ID = 'Your_Sequence_Name';

i am executing this cmd at: E:\IBM\InformationServer\Server\DSEngine> cd bin\dssh
following error msg is displayed: The System can't find the path specified.

i have tried
E:\IBM\InformationServer\Server\DSEngine\bin> cd dssh and cd .\dssh

error is same.
colud you please suggest me on this..

sreddy666 · Post by **sreddy666** » Thu Jun 14, 2012 4:37 am

Hi craig,

i am able to execute .\dssh

E:\IBM\InformationServer\Server\DSEngine\bin>.\dssh
This directory is not set up for DataStage.
Would you like to set it up (Y/N)?N

E:\IBM\InformationServer\Server\DSEngine\bin>.\dssh
This directory is not set up for DataStage.
Would you like to set it up (Y/N)?y
0. Ideal compatibility
1. IN2 compatibility
2. Prime Information compatibility
3. PICK compatibility
4. PI/open compatibility
5. Microdata Reality compatibility

When i select for 0:
Your VOC is configured for Ideal compatibility
Creating file "VOC" as Type 3, Modulo 23, Separation 4.
Creating file "D_VOC" as Type 3, Modulo 2, Separation 1.
Loading your "VOC" file. (Each "*" = 10 records.)
************************************************************************
Loading your "D_VOC" file. (Each "*" = 10 records.)
*
User does not have WRITE permissions to current directory.
User does not have WRITE permissions to current directory.
DataStage Command Language 8.5 Licensed Materials - Property of IBM
(c) Copyright IBM Corp. 1997, 2009 All Rights Reserved.
bin logged on: Thu Jun 14 06:25:14 2012

>

So cani go ahead and execute the cmd after > symbol ???

Thanks in advance..

chulett · Post by **chulett** » Thu Jun 14, 2012 7:13 am

As has been mentioned here many times, you never answer that question "Y". Always launch dssh from either DSEngine or a Project directory. For the former, LOGTO a Project, for the latter you are already logged into the project.

zulfi123786 · Post by **zulfi123786** » Thu Jun 14, 2012 7:40 am

Whe i just moved into datastage had this issue and did not know how and which file to move and from where.

fixed the situation by copying the code of the routine and modifying it to update with the value passed as argument instead of applying " + 1 "

chulett · Post by **chulett** » Thu Jun 14, 2012 7:41 am

True, that's a pretty common thing people seem to do - create an "Update Next Value" version of the routine, then all you have to do is "test" it to make the update.

sreddy666 · Post by **sreddy666** » Fri Jun 15, 2012 6:13 am

Hi Craig,

I have executed .\dssh in DSEngine directory..

while executing :UPDATE SDKSequences USING DICT VOC SET F1 = '192' WHERE @ID = 'E_OPER_UNIT_TBL'

got error: DataStage/SQL: table sdksequence doesn't exist.

when i search in forum.. found that, execute the statement:
CREATE.FILE SDKSequences 2 1 1 and then update the sequence.

a> i have already copied SDKSequence from old project to new project.
b> if i execute create .file statement again will other sequence will be effect (i.e when jobs run.. sequence will start from 1st or will it be start from old sequence no:).

Thanks in advacne..

zulfi123786 · Post by **zulfi123786** » Fri Jun 15, 2012 6:41 am

sreddy666 wrote:b> if i execute create .file statement again will other sequence will be effect (i.e when jobs run.. sequence will start from 1st or will it be start from old sequence no:).

if you are creating the file then you dont have to get it from other environment.

Yes your other sequences will be effected, they would start from 1. If you want the other sequences to start from specific value then you will have to update the file for each of the sequences