Skip to main content
P
ptc-46708811-Visitor
1-Visitor
January 11, 2013
Solved

Implementer and SQL DDL thoughts/standards?

  • January 11, 2013
  • 3 replies
  • 10288 views

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.

    Best answer by smigliorato

    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.

    3 replies

    S
    smigliorato1-VisitorAnswer
    1-Visitor
    January 11, 2013

    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.

    P
    ptc-46708811-VisitorAuthor
    1-Visitor
    January 11, 2013

    Thanks for you reply Sharon.

    If I am understanding correctly we would do the following:

    1. Create an Implementer checkout with a Code of SQLTABL. The source would be a CREATE TABLE statement.
    2. Promote the checkout and the CREATE TABLE statement would be executed.
    3. When we want to add a column to the table we would check out the SQLTABL and modify the CREATE TABLE statement as desired.
    4. Promote the checkout and Implementer would anlayze and determine if a CREATE TABLE or an ALTER TABLE would need to be performed and take the appropriate action.

    Am I understanding the above process correctly?

    A few more questions:

    5. Should CREATE VIEW statements be seperate checkouts?

    6. Should CREATE INDEX statements be a seperate checkout?

    7. Implementer would also properly handle any Referential Constraint and/or Primary Key changes relating to the CREATE TABLE statements when changes are made?

    S
    smigliorato1-Visitor
    1-Visitor
    January 11, 2013

    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.

    P
    ptc-47097801-Visitor
    1-Visitor
    January 16, 2013

    Hi Mike,

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

    P
    ptc-46708811-VisitorAuthor
    1-Visitor
    January 21, 2013

    Any thoughts on how to handle indexes? Are you adding indexes to the same SQL script as the CREATE TABLE statement or are you createing seperate SQL script members (checkouts) for indexes?

    P
    ptc-47097801-Visitor
    1-Visitor
    January 22, 2013

    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

    P
    ptc-46708811-VisitorAuthor
    1-Visitor
    January 22, 2013

    Thanks for the input Tommaso. Do you ever use System i Navigator Index Advisor to create indexes or do you always create them in Implementer and promote them thru the proper environments?

    When a table is re-created or altered are all of the indexes reapplied as well or is that something you would need to do as a re-compile or equivilant in Implementer?

    Terms & ConditionsCookie settingsAccessibility statement