Cannot Insert new records into table!!!

[shamshad]

Hello friends,

I desperately need quick help on this:

This is the error I am getting
"One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains ta"

MY PRODUCT TABLE has Columns as below
ProductID, (Primary Key) Integer
ProductCode, Character (10)
ProductName Character (40)

I am going this.
(1) Taking CSV as a source file and getting distinct ProductCodes,Names
(2) Then pulling distinct ProductCodes from Product Table.
(3) Doing a lookup from CSV to ProductCodes Table and see if the
ProductCode from CSV exits in Table.
(4) If "NO" then I am sending the row to INSERT link, else it goes
to "UPDATE link".
(5) Update works fine. I update only ProductName.

(6) In INSERT, I have am using "KeyMgtGetNextValue("ProductID")
and getting ProductCode, Name from CSV.

Even though I am getting the next value using the KeyMgtGetNextValue
why it is pulling diferent values like 1,5,6,7?

Since the Product Table already has 51 rows (ID from 1-51), this gives error.

Please let me know if there is a way to solve it.

[tcj]

(6) In INSERT, I have am using "KeyMgtGetNextValue("ProductID")
and getting ProductCode, Name from CSV.

Even though I am getting the next value using the KeyMgtGetNextValue
why it is pulling diferent values like 1,5,6,7?

Since the Product Table already has 51 rows (ID from 1-51), this gives error.

Please let me know if there is a way to solve it.

Shamshad can you give us anymore info on KeyMgtGetNextValue. I am guessing that this is a routine?

Do you have the code for this routine?

Tim

[chulett]

Tim, KeyMgtGetNextValue is an 'sdk' routine supplied with DataStage by Ascential. It reads a record from a specific hash file in the current project, gets the requested record (which holds a numeric value) increments it and writes it back to the hash. It's basically a methodology to generate surrogate keys.

Shanshad, there's not really enough information in your post to help you. For example, you never mention the database you are working with or what stage you are using to interface with it. I'd also be curious if there is a numeric error code associated with the error text you posted and where exactly it is coming from - Datastage? your database? It strikes me as kind of an odd error to be getting. Any chance there is a trigger on this table?

Even though I am getting the next value using the KeyMgtGetNextValue why it is pulling diferent values like 1,5,6,7?

I'm not really sure what you are asking here - different from what, what you were expecting? Do you understand how the routine you are calling works? Do you pass in a consistant argument to it? It seems like you are always passing the literal "ProductID" to the routine, but wanted to be sure. It does start at 1 for every 'key' passed to it, so unless you've used it to populate all existing rows in your table, it will have no clue that it should start with 52.

More information about what it is you are trying to do and what you are using to do it will help us help you solve this.

[vmcburney]

By default when you ask for a new sequence number from KeyMgtGetNextValue for ProductID it will start at 1.

Code: Select all

* Attempt to read the named record from the file
  Redu NextVal From SeqFile, Arg1 Else
    * No record by that name.
    * Start the new sequence at 1.
      NextVal = 1

You want it to start at 51.

What you could do is log into Universe and manually update that hash file row to the required value. Perhaps an easier option is to create a job that retrieves the maximum key number from the target database via a MAX command and writes the value to the SDSSequences hash file, overwriting the ProductID row.

I haven't used KeyMgtGetNextValue all that much, I tend to use stage variable counters, let us know how it goes.

[tcj]

Tim, KeyMgtGetNextValue is an 'sdk' routine supplied with DataStage by Ascential. It reads a record from a specific hash file in the current project, gets the requested record (which holds a numeric value) increments it and writes it back to the hash. It's basically a methodology to generate surrogate keys.

Thanks Craig.

I first thought that it was a routine he had written to generate a surrogate key for ProductId.

The problem is with the way the routine is being used rather than with the code in the routine.

Cheers

Tim

[rasi]

KeyMgtGetNextValue is an 'sdk' routine supplied with DataStage by Ascential. The value stored in the hash file can be updated using Universe update command. You will give a name as parameter when using this function which will hold the generated key value. The name you give must not shared by other jobs. Give the name with table name in it so it is easy do change the values using names.


The number gets incremented if the stream of rows passed to it. Regardless whether it got aborted or inserted into the target table the number gets increased. It is always good practice to have this as the last step in inserting record to the table.

If you have dev, test and prod environment make sure that this hash file is updated with the right number.

Thanks
Siva

[SANOJ]

Hi,

To add some more detail to other participants' contribution :

We use the SDKSequences hashed file and the KeyMgtGetNextValue routine to generate unique Database keys. As stated already higher, be aware that this routine does NOT look at your product database table itself to fetch the maximum key. It is a simple counter that adds 1 each time you call this routine.

What you can do as a simple solution is : run your program as many times until the counter of your 'ProductId' in the SDKSequences hashed file (that you can normally find under ...\Ascential\DataStage\Projects\
your_project_name) has come to 51. From than on, new keys will be accepted in your database table.
Some further 'caveats' :
- don't use other programs that also insert new keys into your product table, because then your product key in your table will be out of sync with your SDKSequences hashed file from the KeyMgtGetNextValue routine.
- each time you run a job and you come into the Insert stage (where you call keymgtgetnextvalue), the value of your key field will be + 1 (so if you use this for testing and don't update your database or remove records from your database, the counter keeps on going up). But GAPS in key sequences shouldn't be an issue, as long as the keys remain unique.
- if you update generate TWO keys simultaneously, than use routine KeyMgtGetNextValueConcurrent, otherwise the second key will inherit the value from the other key!

If you need to update the key sequence more than once, you can create a simple job that updates the hashed file SDKSequence with a value you have manually put in some file.

Eg Input stage CSV file, that you manually fill in

Key_Name : char 50 eg 'ProductId'
Next_Value : integer 50 eg 51

Transform stage (just to pass the values)

Hashed file stage : you connect to the hashed file SDKSequences (under the directory as mentioned above - please check position of this file on your system) with Key_Name marked as key field and Next_Value as second field; your input will override the counter in the hashed file.

Good luck,
Wim

__________________
"Carpe Diem"

[shamshad]

First of all, many thanks to everyone here who took time to reply.
Let me try to explain what's going on.

There is one separate job that does a CLEAN insert into all dimension and facts tables. This job is run ONLY THE FIRST TIME. Before this job runs, it run a RESETKEY job that makes all the counters to 1 for all PrimaryKey field. ResetKey is from the SDK with Name/Value pair as someone mentioned here. This works fine and all tables starts with 1 for PrimaryKeys.

After that there is another job, that runs UPDATE and INSERTS. Now as long as the RESETKEY is not run in between, it always picks up the next value from the Hash File and gives the next value.

Like during the first load, Product Table was 51 rows and the HASH FILE has 51 as last value.

When the INSERT/UPDATE job was run it gave 52 which was JUST the way it should work. Then after some steps, I made some mistakes and had to RELOAD the DATA again and also RAN RESET KEY JOB.

Here started the problem. My Product now has 51 rows once again but the HASH FILE has 1 as ProductKEY.

Now whenever I ran the UPDATE/INSERT it took value from HASH file, which is 1 and gave me the next number that was 2. Now w is already there in the Product Table, so it throws the errors.

Rather than keeping an eye to make sure that my HASH file that has the KEYName/Value is always in SYN, I would better want to get the last value from the database and add 1 to it and then run the UPDATE/INSERT job. That will solve the issues and I no more have to me worried about the HASH FILE generated by DataStage.

I hope I am giving enough information. I will try to get the LAST VALUE from the Database.

Hey I am just new to Datastage and trying my best to do whatever I can. One thing I can definitely say is, this forum is filled with extremely supportive people who give prompt reply.

Thank you once again guys for helping me out. Let me know how to get LAST KEY from Database and add 1 to it.

[ray.wurlod]

The SQL is obviously of the form SELECT MAX(column) FROM TABLE;

You can create a DataStage job that collects this value, and writes it directly into the SDKSequences hashed file (the one used by the SDK Key Management routines) where the key value is defined as a constant in the Transformer stage.

Code: Select all

  database  ----->  Transformer  ----->  HashedFile
                                       (SDKSequences)