Skip navigation
530 Views 16 Replies Latest reply: Jan 25, 2013 5:55 AM by ptc-4709780 RSS
ptc-4670881 Copper 16 posts since
Oct 17, 2012
Currently Being Moderated

Jan 11, 2013 6:20 AM

Implementer and SQL DDL thoughts/standards?

We are on the System i (aka AS/400) using Implementer.  We are transitioning from defining our physical and logical files using DDS to using SQL DDL to define SQL Tables and Views.  I would like to get some thoughts and opinions from other users of Implementer that are using SQL DDL as to how they structure their SQL DDL script source members within Implementer and promote them through their various environments?

 

Any input/thoughts would be greatly appreciated.

  • ptc-4058746 PTCEmployee 12 posts since
    Sep 26, 2012
    Currently Being Moderated
    Jan 11, 2013 6:37 AM (in response to ptc-4670881)
    Re: Implementer and SQL DDL thoughts/standards?

    From an Implementer standpoint, the process is quite similar, although behind the scenes, things work differently. But if you have been using the optimize flag for DDS file types, the process would be more familiar. SQL requires that the optimize flag be set to Y.

    You would still check out your files (Implementer provides all of the SQL file type object codes for you), make your changes in Development and compile there. You just need to change your source. Implementer will automatically handle the drop and the alter table statements for you.

    When you promote forward, the process is exactly the same as traditional files. If the compile-required flag is set to Y, Implementer still compiles the tables and other DDL files in the work library. When you do the move however, if the file object exists in the target library, Implementer automatically generates your alter table statement for you and does the CHGPF/CHGLF against the existing file. If the files do not exist in the target, the compiled file in the work library is moved to the target, just as a traditional file would be.

    Related object processing is much more enhanced for SQL because you can use techniques such as view within view. The current release 10.2 can handle all of these new SQL relationships for you.

    As far as converting an existing DDS file to a DDL table, there is a KnowledgeBase article on our website that gives you the detailed steps on how to accomplish that.

    Please let me know if you have any other questions.

      • ptc-4058746 PTCEmployee 12 posts since
        Sep 26, 2012
        Currently Being Moderated
        Jan 11, 2013 7:34 AM (in response to ptc-4670881)
        Re: Implementer and SQL DDL thoughts/standards?

        You are definitely understanding the steps correctly.

        Yes, there are object codes for both views and indexes. They should be separate checkouts, and are treated like traditional logical files, as far as related objects are concerned.

        And yes, Implementer can handle referential constraints and primary keys.

  • ptc-4709780 Copper 7 posts since
    Oct 5, 2012
    Currently Being Moderated
    Jan 16, 2013 3:34 AM (in response to ptc-4670881)
    Re: Implementer and SQL DDL thoughts/standards?

    Hi Mike,

    we are defining the same process (from DDS to DDL) using always Implementer, it works very fine!

    • ptc-4709780 Copper 7 posts since
      Oct 5, 2012
      Currently Being Moderated
      Jan 22, 2013 12:11 AM (in response to ptc-4670881)
      Re: Implementer and SQL DDL thoughts/standards?

      In my opinion is correct to create an association one item/one check-out. That for a better traceability. Therefore it could be happen to modify the index Key and not to modify the table. So my suggestion is to create a separate Sql script member for every source. Thanks Tommaso

        • ptc-4709780 Copper 7 posts since
          Oct 5, 2012
          Currently Being Moderated
          Jan 23, 2013 3:05 AM (in response to ptc-4670881)
          Re: Implementer and SQL DDL thoughts/standards?

          Hi Mike,

          i suggest you to use always Implementer.

          When a table has modified, all indexes/Views/pgm in automatic are pulled as recompiled. It works very fine with *file objects!

          at disposition for further info...Tommaso

           

          P.S What Implementer version are you using?

            • ptc-4709780 Copper 7 posts since
              Oct 5, 2012
              Currently Being Moderated
              Jan 25, 2013 12:48 AM (in response to ptc-4670881)
              Re: Implementer and SQL DDL thoughts/standards?

              Hi Mike,

              could you send me an example?? What do you mean for "unique and che ckeck constraints"?

               

              Constraints like Primary Key?? Constraints like Foreign Key or integrity check on the column definition??

               

              Thanks,

              Tommaso

                • ptc-4709780 Copper 7 posts since
                  Oct 5, 2012
                  Currently Being Moderated
                  Jan 25, 2013 5:08 AM (in response to ptc-4670881)
                  Re: Implementer and SQL DDL thoughts/standards?

                  Ok...please send me the following print screen:

                  -QA environment configuration.

                  -SQLTABL object code.

                   

                  Probably you need to set any parameter.

                  In my qa environment all works fine.

                   

                  Bye,

                  Tommaso

                    • ptc-4709780 Copper 7 posts since
                      Oct 5, 2012
                      Currently Being Moderated
                      Jan 25, 2013 5:30 AM (in response to ptc-4670881)
                      Re: Implementer and SQL DDL thoughts/standards?

                      I apologize Mike, but for display object code i mean this:

                       

                      Object code  . . . . . . . . . :   SQLTABL    SQL Table                         

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

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

                      Object attribute . . . . . . . .   PF                                         

                      Source member type . . . . . . .   TABLE                                      

                      Default source file  . . . . . .   QTBLSRC                                    

                      Creation sequence  . . . . . . .    100         1-9999                        

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

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

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

                      Remove src in from env . . . . .   Y            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(*NONE)                                          

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 3 points
  • Helpful Answers - 1 points