cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - You can change your system assigned username to something more personal in your community settings. X

When implementing a new constraint, what moves into production?

sdamery
1-Newbie

When implementing a new constraint, what moves into production?

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?

8 REPLIES 8
DawnBlohm
5-Regular Member
(To:sdamery)

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.

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
5-Regular Member
(To:sdamery)

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.

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
5-Regular Member
(To:sdamery)

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.

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
14-Alexandrite
(To:sdamery)

Hello Scott,

Did Dawn help you resolve this issue? If so, could you mark her solution as Correct Answer, 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 Helpful Answer.

Thanks,

Kael


Kind Regards,
Kael Lizak

Senior Technical Support Engineer
PTC Integrity Lifecycle Manager

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.

Top Tags