Topic
10 replies Latest Post - ‏2012-07-02T10:01:46Z by PaulBailey
PaulBailey
PaulBailey
5 Posts
ACCEPTED ANSWER

Pinned topic Organising SQL scripts

‏2012-06-27T11:38:54Z |
The task to create a new database using SQL DDL instead of DDS has fallen to me. I know my way around SQL fairly well, but my experience with creating and maintaining databases with SQL on the IBM i is limited. (My experience in other areas of IBM i database/source management is fairly extensive.)

I have written five scripts so far and it suddenly occurs to me that this is going to be a maintenance nightmare if I continue just writing SQL script after SQL script as source members with only source member descriptions giving any idea of what is in the member. Obviously a 'find string' feature like that in WDSC (or PDM) will help a little but I really need a way of organising these scripts so that it is easy to find the latest CREATE or AMEND table script for a particular table and link together scripts where constraint references need to be considered if one table changes.

What methods and/or tools are people using to keep their database creation/maintenance SQL scripts organised?

I have extensive experience of a similar role using SQL Server where the scripts were not kept as the latest information could be produced by SQL Server Management Studio on the fly. Can this be done with an IBM i database? Am I needlessly focusing on scripts as I would DDS source files when there is a better way?

Any and all constructive advice would be appreciated.
-Paul.
PS I have no access to any source control that will work on the IBM i at the moment. This lack has been an ongoing fight with management for a long time, so any suggestions that revolve exclusively around source control software solutions will not be helpful.
Updated on 2012-07-02T10:01:46Z at 2012-07-02T10:01:46Z by PaulBailey
  • LuisRodriguez
    LuisRodriguez
    15 Posts
    ACCEPTED ANSWER

    Re: Organising SQL scripts

    ‏2012-06-27T13:24:20Z  in response to PaulBailey
    Paul,

    You can easily re-create IBM i SQL object's scripts on the fly, using iSeries Nav. Then only point I see with this is: what happens if, by any reason, you lose the original object? The object info is then lost.

    Also, if you already have a script stored as a source member, is really easy to create similar objects (say, a view that differs in only a few fields or a particular selection) by just copying the original script under the new object's name.

    Regarding info about constraints these can be found easily using iSeries Nav. You can even create a (somewhat simple) map of your Schema objects and relationships.

    Hope this helps,

    Luis Rodriguez.
    • PaulBailey
      PaulBailey
      5 Posts
      ACCEPTED ANSWER

      Re: Organising SQL scripts

      ‏2012-06-28T08:41:45Z  in response to LuisRodriguez
      Sorry Luis,

      I'm sure I posted a response to your post yesterday, but I can no longer see it.

      In essence, I have tried the generate SQL option on the i Navigator tool, and it does produce scripts almost the same as the ones I use. But, just like the SQL Server environment I worked on before, you need to keep track of the database changes externally and that is not ideal where I currently work as the change control doesn't go to that level.

      I think the problem I am having is that I am trying to treat SQL scripts as DDS source to help keep track of changes and references through source comments, but scripts are very different to DDS source in that a CREATE TABLE script will probably never be run again after the table is in the live environment because all database changes will be handled by ALTER TABLE scripts. Should I keep a long string of ALTER TABLE scripts with a CREATE TABLE script, or should I change the CREATE TABLE script after every database table change and then delete the ALTER TABLE script? Neither option helps find out (e.g. in a year or two) why a particular field was added or who changed the unique key, etc.
      -Paul.
      • LuisRodriguez
        LuisRodriguez
        15 Posts
        ACCEPTED ANSWER

        Re: Organising SQL scripts

        ‏2012-06-28T13:09:11Z  in response to PaulBailey
        Paul,

        I think I can understand your dilemma… You want to keep track of why you made a particular change to your database. The problem with source members, as I see it, is that often they get misplaced or out of sync. Sooner or later someone will change the source or delete it by mistake (at least, it has happened in our shop).

        Now, as I see it, short of having a CMS to keep track of the changes (which I know is a not an option for you), I wonder if you have checked the “COMMENT ON” SQL statement? It allows you to have a “REMARKS” made to your table, trigger, column, etc. of up to 2K chars in length, and it gets written to the SYSCOLUMNS table. So maybe you could write something like:

        ALTER TABLE @520/TEST ALTER COLUMN C_AME SET DATA TYPE CHARACTER(15 ) NOT NULL WITH DEFAULT ''
        COMMENT ON COLUMN @520/TEST (C_NAME IS '2012-06-28 – Changed because...')

        And then insert your ALTERs (and COMMENTS) just below your original CREATE TABLE source.
        HTH,
        Luis Rodriguez
        • PaulBailey
          PaulBailey
          5 Posts
          ACCEPTED ANSWER

          Re: Organising SQL scripts

          ‏2012-06-28T14:19:47Z  in response to LuisRodriguez
          Hi Luis,

          That is an interesting idea. It should be possible to encode a date, name and service request number on the comment for the table and or a column. Can a previous comment be appended so that it is not overwritten after a second or third ALTER TABLE statement? Or will I need to retrieve the comment for every column before the ALTER?
          -Paul.
          • LuisRodriguez
            LuisRodriguez
            15 Posts
            ACCEPTED ANSWER

            Re: Organising SQL scripts

            ‏2012-06-28T15:17:21Z  in response to PaulBailey
            Paul,

            AFAIK, Only some types of objects can have COMMENTS added on (opposed as replaced to). Maybe you could create a little UDF that allows you to write a simlpe SQL instruction as SET REMARK(TABLE, COLUMN, ServiceNum) = 'New Comment' . Behind the scenes, this would retrieve the old value from SYSCOLUMNS, ADD a new delimiter, date and time, Service Number and execute a new COMMENT ON statement.

            Regards,

            Luis
            • LuisRodriguez
              LuisRodriguez
              15 Posts
              ACCEPTED ANSWER

              Re: Organising SQL scripts

              ‏2012-06-28T16:15:18Z  in response to LuisRodriguez
              Paul,

              It seems I made a mistake in my last post, as you cannot add a COMMENT over one that already exists. I got confused because for PACKAGE there is a version number...

              Regards,

              Luis
              • LuisRodriguez
                LuisRodriguez
                15 Posts
                ACCEPTED ANSWER

                Re: Organising SQL scripts

                ‏2012-06-28T16:57:08Z  in response to LuisRodriguez
                Paul,

                Checking my last post I realize that maybe it is not clearly redacted. I'm afraid that my translating circuits aren't up to par this morning (my native language is Spanish)...

                What I wanted to say is that you cannot append to an existing COMMENT, only overwrite it.

                Regards,

                Luis
                • PaulBailey
                  PaulBailey
                  5 Posts
                  ACCEPTED ANSWER

                  Re: Organising SQL scripts

                  ‏2012-07-02T10:01:46Z  in response to LuisRodriguez
                  Thanks Luis,

                  Another reasonable idea with the home-made procedure to write/append comments.

                  Generally, the answer to my question is not clear without a CMS. Using the COMMENT feature within SQL and various member types I can approximate a version history. Unfortunately, the whole thing requires a lot of concentration from the developers who will all be new to DB maintenance through SQL instead of DDS and so I will just have to push extra hard for a CMS so there is less onerous new stuff for the developers to get wrong on every file change.
                  -Paul.
  • B.Hauser
    B.Hauser
    248 Posts
    ACCEPTED ANSWER

    Re: Organising SQL scripts

    ‏2012-06-28T05:44:53Z  in response to PaulBailey
    I normally create my database object with System i Navigator.

    After having created or modified any of the database objects I use reverse engineering (Generate SQL) and store the Script in a source physical file member with the system object name (you also can store the Script somewhere in the IFS).

    I split the scripts into 2 source files. In the first source file I store all scripts for objects that cannot be recreated without problems but must be altered instead (for examples tables). In the second source file I store all scripts for database objects that can be recreated for examples Indexes, Views.

    In the member type I specify what object it is.
    If there are dependencies between database objects (for examples referenctial integrities), I add a counter to the member type, so if I have to recreate my database objects (for example for a test environment) I can do it in the right sequence.

    For examples:
    All tables with out foreign keys get member type TXT_TBL01
    Tables that depend directly on these tables get member type TXT_TBL02.
    Same thing for views which get member type TXT_VW01, TXT_VW02 ...

    Birgitta
    • PaulBailey
      PaulBailey
      5 Posts
      ACCEPTED ANSWER

      Re: Organising SQL scripts

      ‏2012-06-28T08:26:03Z  in response to B.Hauser
      Thanks Birgitta,

      Using the custom member types is a good idea. It will take a little setting up in the Rational Developer for Power i, and some extra education for the other programmers, but it is workable.

      Do you keep ALTER TABLE scripts, or do you just maintain the original CREATE TABLE scripts and remove the ALTER TABLE scripts after the changes have gone live?

      In your TXT_TBL02 members, how do you find the members they relate to? Programmer-maintained source comments, FNDSTRPDM, or some other option? I'm wondering if there is a way to use the INCLUDE statement within the script to link my own source members together, but I haven't worked that idea out properly yet.
      -Paul.