IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 3 replies
  • Latest Post - ‏2018-04-20T06:05:10Z by IHD-MS
IHD-MS
IHD-MS
3 Posts

Pinned topic GUI Tools for SQL development on IBM i 2018

‏2017-12-07T09:38:50Z |

Hi all,

 

in 2011, Dav_G asked in this forum which tools are recommended for modelling a database on IBM i. I have to ask the same, because the answer then--using a mixture of IBM Data Studio Client, IBM i Navigator and a third-party software--doesn't seem ideal to me.

When I was starting my project, I first used MySQL Workbench to design a raw concept and then translated the SQL output in the DB2 dialect but now it's getting more complex, and I'm looking for a better solution.

I was very excited when I discovered IBM Data Studio Client, but disillusion followed soon: To me, it's difficult to find what I want and to see what I am actually doing. Maybe it's because I don't know how to use it correctly, but I don't find a documentation of version 4.1.2 and the included help is not really helpful (it was disappointing to first find interesting looking headlines, but after choosing them the help text is missing). On Youtube there are a few videos by "Analytics Platform Tool", but they are mute and thus very difficult to follow and seem to focus on DB2 for Linux and Windows.

 

So, which tools do you use and recommend? Or: Do you know a good read on Data Studio Client with IBM i so I can keep using IBM Data Studio?

 

Thanks,
Markus

Updated on 2018-04-05T15:15:54Z at 2018-04-05T15:15:54Z by IHD-MS
  • Phil5471
    Phil5471
    5 Posts
    ACCEPTED ANSWER

    Re: GUI Tools for SQL development on IBM i 2018

    ‏2018-04-19T21:32:10Z  

    Data Studio is certainly worth the price. However, even with its short comings it is an effective tool, in my opinion.  I have yet to find a tool that does everything the way I think it should or include the features I think are vital.

    While I don't believe it was ever planned to include DB2 for i at least it appears in the list of supported databases.

    I agree that help included with DS is really poor and documentation is lacking.  We learned by doing with some frustration along the way.  I created a large Power Point presentation and conducted training classed for our developers who knew nothing about modelling.  All were experienced developers making the transition from DDS to DDL.  DS was beneficial for us in this transition.

    The requirement in our shop is to use DS to model all new tables, indexes and views.  Check constraints and referential constraints are easy to set up.  The diagrams are very useful, too.  (Wish there was an option to publish like Infosphere Data Architect.)  After the tables, etc., are defined in DS we generate the DDL which is used to create the tables.  (Our standard is 1 DDL statement per table including check and referential constraints, 1 per index, 1 per view.)  All models are reviewed before anything is promoted to our production system.

    Almost all of our I/O processes use SQL so using the system generated RCDFMT value isn't an issue.  If it's important in your shop simply add it to the generated DDL before creating the table.  You can add the GRANT statements, too.  I suspect this is what you've been doing.  We have to edit the DDL for indexes to specify the SYSTEM name.  Also, we run commands to set the authority and ownership attributes of the objects after they are created.  Yes, it's annoying and cumbersome but after doing it for a few years we don't really think about it much.

    I use Infosphere Data Architect which uses parts of DS as its foundation but does have some additional features. It's not free though.

    Good luck.

    Phil

  • IHD-MS
    IHD-MS
    3 Posts

    Re: GUI Tools for SQL development on IBM i 2017

    ‏2018-04-05T15:15:17Z  

    Hi again,

     

    in the meantime I'm using IBM Data Studio to some extent, but it bothers me, that it is not as good as it could be.

    For example if I generate SQL from an existing table in System i Navigator 7 R1, it will generate a CREATE TABLE statement with the correct record format (RCDFMT) and also GRANT statements to set the user privileges right.

    IBM Data studio can't do this. Or can it? I would prefer it over Navigator because there are some really nice features.

     

    So, is there someone who wants to give me a tip with which tool to design my database on i or how to get the best from Data Studio with DB2 on i?

     

    Best regards,
    Markus

  • Phil5471
    Phil5471
    5 Posts

    Re: GUI Tools for SQL development on IBM i 2018

    ‏2018-04-19T21:32:10Z  

    Data Studio is certainly worth the price. However, even with its short comings it is an effective tool, in my opinion.  I have yet to find a tool that does everything the way I think it should or include the features I think are vital.

    While I don't believe it was ever planned to include DB2 for i at least it appears in the list of supported databases.

    I agree that help included with DS is really poor and documentation is lacking.  We learned by doing with some frustration along the way.  I created a large Power Point presentation and conducted training classed for our developers who knew nothing about modelling.  All were experienced developers making the transition from DDS to DDL.  DS was beneficial for us in this transition.

    The requirement in our shop is to use DS to model all new tables, indexes and views.  Check constraints and referential constraints are easy to set up.  The diagrams are very useful, too.  (Wish there was an option to publish like Infosphere Data Architect.)  After the tables, etc., are defined in DS we generate the DDL which is used to create the tables.  (Our standard is 1 DDL statement per table including check and referential constraints, 1 per index, 1 per view.)  All models are reviewed before anything is promoted to our production system.

    Almost all of our I/O processes use SQL so using the system generated RCDFMT value isn't an issue.  If it's important in your shop simply add it to the generated DDL before creating the table.  You can add the GRANT statements, too.  I suspect this is what you've been doing.  We have to edit the DDL for indexes to specify the SYSTEM name.  Also, we run commands to set the authority and ownership attributes of the objects after they are created.  Yes, it's annoying and cumbersome but after doing it for a few years we don't really think about it much.

    I use Infosphere Data Architect which uses parts of DS as its foundation but does have some additional features. It's not free though.

    Good luck.

    Phil

  • IHD-MS
    IHD-MS
    3 Posts

    Re: GUI Tools for SQL development on IBM i 2018

    ‏2018-04-20T06:05:10Z  
    • Phil5471
    • ‏2018-04-19T21:32:10Z

    Data Studio is certainly worth the price. However, even with its short comings it is an effective tool, in my opinion.  I have yet to find a tool that does everything the way I think it should or include the features I think are vital.

    While I don't believe it was ever planned to include DB2 for i at least it appears in the list of supported databases.

    I agree that help included with DS is really poor and documentation is lacking.  We learned by doing with some frustration along the way.  I created a large Power Point presentation and conducted training classed for our developers who knew nothing about modelling.  All were experienced developers making the transition from DDS to DDL.  DS was beneficial for us in this transition.

    The requirement in our shop is to use DS to model all new tables, indexes and views.  Check constraints and referential constraints are easy to set up.  The diagrams are very useful, too.  (Wish there was an option to publish like Infosphere Data Architect.)  After the tables, etc., are defined in DS we generate the DDL which is used to create the tables.  (Our standard is 1 DDL statement per table including check and referential constraints, 1 per index, 1 per view.)  All models are reviewed before anything is promoted to our production system.

    Almost all of our I/O processes use SQL so using the system generated RCDFMT value isn't an issue.  If it's important in your shop simply add it to the generated DDL before creating the table.  You can add the GRANT statements, too.  I suspect this is what you've been doing.  We have to edit the DDL for indexes to specify the SYSTEM name.  Also, we run commands to set the authority and ownership attributes of the objects after they are created.  Yes, it's annoying and cumbersome but after doing it for a few years we don't really think about it much.

    I use Infosphere Data Architect which uses parts of DS as its foundation but does have some additional features. It's not free though.

    Good luck.

    Phil

    Thank you very much, Phil!

    It's definitely worth the price. I hope this was a joke, because we never paid for it. "IBM Data Studio is a fully licensed product available at no charge and with no time restrictions." (https://www.ibm.com/developerworks/downloads/im/data/index.html).

    The longer I'm working with it the better I learn to cope with it. Just recently I started to put the additional statements (GRANT and  RCDFMT) in a text file that I just leave opened in DS so I can easily add them to the generated DDL statements. I'm still in development of a bigger app, and despite all efforts to intelligently model the tables, there are stages where you have to reconsider your concept and apply little changes. I now know when I better restart DS to have it refresh all contents and like you said, "after doing it for a few years we don't really think about it much", I am already starting not to think about it.

    So, is the conclusion of this post and its lack of answers that most of IBM i developers don¹t use any tools for database modelling?

    Markus