Promoting SQLTABL through Implementer

[bbrock]

Hi, I'm on IM ver 10.2 and our IBM i is at 7.1. We've just begun to experiment with DDL. I was able to successfully create a new table and promote through IM. However, when I made a change to the CREATE TABLE statement and promoted it through IM, the move failed. Looking in the joblog, I see an error CPF320A "member cannot be removed". I read in this forum that IM should recognize the change in the CREATE TABLE and automatically use an ALTER TABLE to modify the existing table. Does anyone have any suggestions?

Object code  . . . . . . . . . .   SQLTABL  SQL table (source-based)          

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

Object type  . . . . . . . . . :   *FILE                                      

Object attribute . . . . . . . :   PF                                         

Source member type . . . . . . :   SQLTABL                                    

Default source file  . . . . . :   QSQLSRC                                    

Creation sequence  . . . . . . :   0100         1-ZZZZ                        

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

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

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

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

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

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

Creation command . . . . . . . :   RUNSQLSTM SRCFILE(#SRCLIB/#SRCFIL) SRCMBR(#S

RCMBR) COMMIT(*NONE) DFTRDBCOL(#FILLIB)                                        

This is the test script. I first created the table with a single field CACUST with a PRIMARY KEY constraint. Next, I check it out as a change in IM and added the CAHPC field.

======================================

CREATE TABLE CUSTADD (                                                   

  CACUST CHAR(6) CCSID 37 NOT NULL DEFAULT '' ,                          

  CAHPC CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,                           

  CONSTRAINT Q_CUSTADD_CACUST_00001 PRIMARY KEY( CACUST ) )              

  RCDFMT RCUSTADD    ;                                                   

LABEL ON TABLE CUSTADD                                                   

  IS 'Customer additional info' ;                                        

LABEL ON COLUMN CUSTADD                                                  

( CACUST IS 'Customer             ' ,                                    

CAHPC IS 'High                profile             customer' ) ;         

LABEL ON COLUMN CUSTADD                                                  

( CACUST TEXT IS 'Customer' ,                                            

CAHPC TEXT IS 'High profile customer' ) ;                               

GRANT ALTER , DELETE , INDEX , INSERT , REFERENCES , SELECT , UPDATE     

ON CUSTADD TO QPGMR WITH GRANT OPTION ;                                  

[DawnBlohm · Accepted solution]

Hi Brian,

I am going to ask that you log a technical support case for this issue.  We will need to see the entire joblog to get a complete picture of what is happening here.  The only thing I notice in your object code is that you should change DFTRDBCOL(#FILLIB)  to DFTRDBCOL(*NONE).  We may also be having a problem with the primary key constraint on the 10.2 release (current version is 11.2).

Thanks for your cooperation.

Dawn Blohm

[bbrock]

We are currently upgrading Implementer and applying latest PTFs. I will try again sometime after that. Thanks!

[bbrock]

Well, several projects later and I'm back on the hunt to figure out how to promote requests for type SQLTABL for DDL source. The creation process works great. But if the member is checked out as a change, the move requests fails. I looked at the compile from IM and it looked like it was trying to compile it as a DDS. I attached some documents showing the set up of the object code SQLTABL and the results of the failed move. Is there something that I'm missing here? thx.