When implementing a new constraint, what moves into production?

[sdamery]

Hello,

I have created a table in production with a primary key constraint.

I want to add a foreign key constraint.

I use a source member that gets moved and then RUNSQLSTM with a special command that adds the constraint to the file.

normally with a table change we promote the table but if it is just a constraint change, we dont just move the constraint? How do we move a constraint into production if there is no object to move?

What are the steps?

When we promote a table change we create/recreate the table in our staging area and move it into the production area. What do you move when it is just a new constraint?

[DawnBlohm]

Hi Scott,

You can use a source only object code like this and put the request special command after move to RUNSQLSTM:

Note, you might have to change the source type and source file to match yours:

Object code . . . . . . . . . . SQLSRC SQL source only

Activity flag . . . . . . . . : 1 1=Active, 0=Inactive

Object type . . . . . . . . . :

Object attribute . . . . . . . :

Source member type . . . . . . : SQLSRC

Default source file . . . . . : QSQLSRC

Creation sequence . . . . . . : 1000 1-ZZZZ

Special characteristics . . . : *DATA, *MERGE, *MAINT, ...

Object authority . . . . . . . : *KEEP *KEEP, *GRANT

Remove obj in from env . . . . : N Y=Yes, N=No

Remove src in from env . . . . : Y Y=Yes, N=No

Creation process . . . . . . . : M C=Compile, M=Move

Archive in PTC Source . . . . : N Y=Yes, N=No

Creation command . . . . . . . : CPYF FROMFILE(#WRKSRCLIB/#WRKSRCFIL) TOFILE(#PGMLIB/#SRCFIL) FROMMBR(#OBJECT) TOMBR(#OBJECT) MBROPT(*REPLACE) CRTFILE(*YES)

If you have any questions or problems, feel free to reach out to log a support case, either here online or by phoning 1-800-477-6435.

[sdamery]

So will this create the constraint on the file in Production or move the file with the constraint to production? Which would also require data handling unless you use alter table statements...I just dont understand your process procedures.

It should be as easy as running alter table over the current table.

[DawnBlohm]

Scott,

1. If you checkout the table, change the source to add the constaint and promote - Implementer will perform an alter table command during promotion.
2. If you simply wanted to handle the adding of the constraint as a separate source member and performing your own RUNSQLSTM, that's when you would use the object code.

Please let me know if you have any additional questions.

[sdamery]

OK, So I already use option 2 and the special command to add the constraint to the Table in my staging library.

My question is, Now I need that constraint on the table in the Production library not just the staging library. Will a move-distribution move just a constraint without a table? I am afraid I have to move the whole table to get the constraint from my staging library table to my production library table.

[DawnBlohm]

If you are checking out and promoting the source member, you would promote it to production and it would create the constraint when you do the after move special command RUNSQLSTM.

Even if you had checked out the table, the data is not moved or copied during the Alter Table command that executes during promotion.

Call if you have questions.

[sdamery]

If we follow this process of creating an SQL script member to be moved and run in the various environments, will implementer know which ALTER TABLE scripts to run or will it run a DROP and then ADD for existing ADD constraints or when we run the SQL script with a new constraint do we need to add the DROPs before all the previous ADD scripts so that it doesnt error out?

Example:

I create Table TA and promote to prod

then I want to add a Constraint CA using an SQL member and special command to add the constraint CA to table TA in prod

then later I want to add another constraint CB, in the SQL script do I need to add a DROP CA before the ADD CA and ADD CB constraints or will implementer know to do that. Else it will throw an error on the ADD CA script in the SQL member when table TA already has CA.

I am not clear where implementer is helping and where it needs help.

[KaelLizak]

Hello Scott,

Did Dawn help you resolve this issue? If so, could you mark her solution as , so that anyone else running across this problem can find it in a search?

If not, please let us know what is still outstanding. If her answer partially helped, you can always mark what helped as a .

Thanks,

Kael

[sdamery]

Just tried to check out the table add the constraint and DIST-MOVE the table with the contraints (*nothing on the Table changed but the new constraints)

When the table was moved it kept the data but it did not carry the new constraints. almost like implementer thought because the table (outside of constraints) did not change and did not DIST-MOVE it?

Looks like the only way to promote constraints is to run the script in each environment.