5 replies Latest Post - ‏2012-10-17T05:02:34Z by B.Hauser
2 Posts

Pinned topic Rational -- UDFs & Stored Procedures

‏2012-10-13T02:54:54Z |
Can someone explain why my company paid a decent amount of money for Rational for i, however in order to create SQL UDF's or stored procedures I still must resort to using the IBM i Navigator SQL scripting utility? I have tried everything including copying the Navigator's JDBC settings exactly and am unable to create UDFS in Rational. The same goes for Data Studio. Additionally, using naming = System one cannot see any tables. It seems like the abilities of the product are somewhat lacking considering STRSQL and Navigator work better.

What do people here use for SQL development? Am i missing some secret setup?

I feel a bit short changed that Rational is not able to handle such tasks.
Updated on 2012-10-17T05:02:34Z at 2012-10-17T05:02:34Z by B.Hauser
  • canutri
    4 Posts

    Re: Rational -- UDFs & Stored Procedures

    ‏2012-10-13T23:16:08Z  in response to JamesStrick

    Setting up Rational to view DB2 for i database objects can be done, but it requires some obscure driver setup. You cannot use naming=sysem (AFAIK), so you must be willing to use the dot qualifier instead of the native slash qualifier for tables. This also means you cannot use library list (which is important in our environment as we have 5 libraries for our ERP application). Because of this we also define an additional driver & connection using naming=system.

    Create a driver definition via Window>Preferences>Data Management>Connectivity>Driver Definitions
    Click the Add button
    From the New Driver Definition dialog, Vendor Filter select DB2 for i5/OS
    Select Other Driver
    Provide a meaningful name in Driver name (i.e. MegaERP)
    Click the Jar List tab
    Click the Add JAR/Zip button
    Navigate to you jt400.jar location, click Open
    Click the Properties tab
    Enter Connection URL: jdbc:as400:<myIBMiDB>;prompt=false;)
    Database: <MegaERP>
    Driver Class:
    Password: secret
    User Id: ItsMe
    Click Ok

    Now with a properly defined driver you can create a Database Connection in the Data perspective.
    From the Data Source Explorer view, Click the New Conneciton Profile button
    Select DB2 for i5/OS in the database manager pane
    Select the driver definition you created above from the JDBC Driver drop-down (all the normal driver properties should already be set)
    Provide the User name and Password
    Click the Save Password checkbox
    Click Finish

    Viola! you should now have a functioning Database Connction providing you with drill-down into the schema list and db objects.

    For simple SELECTS, I may use the default SQL editor. However, when I really need the editor to be DB2 for i aware, I will use Navigators Run SQL Script. When using projects via Data Project Explorer (as we do with RTC), you can open the Run SQL Scripts editor by right-clicking the SQL script and select Open With>System Editor.


    • JamesStrick
      2 Posts

      Re: Rational -- UDFs &#38; Stored Procedures

      ‏2012-10-14T02:33:07Z  in response to canutri
      I have the same setup, two different connections (SQL and SYS). I cannot use SQL to create functions and SPROCS as they ignore the library list which we utilize. However using the SYS connection i can never drill down into tables.

      Currently i have to perform the following gymnastics to develop for SQL on i:

      Edit in RDP so that i have some source highlighting (though it misses some statements and the verifier is useless). Once i have it edited i have to copy it over to Navigator's run SQL scripts to see if it will run. Once i have corrected any code errors and crated a procedure, then i must paste back into RDP so i can save the source off. It just seems ridiculous given that RDP costs $800. Can the RDP team not copy the drivers utilized by Navigator? Data Studio aslo seems to be not quite supported on IBM i which i find disappointing.
      • krmilligan
        446 Posts

        Re: Rational -- UDFs &#38; Stored Procedures

        ‏2012-10-16T15:29:45Z  in response to JamesStrick
        Have you tried including SET OPTION NAMING=*SYS into the source of your procedure or function? That should override the connection using *SQL naming.
        • B.Hauser
          250 Posts

          Re: Rational -- UDFs &#38; Stored Procedures

          ‏2012-10-17T05:02:34Z  in response to krmilligan
          I couldn't find an option to switch the naming conventions in RDp either.

          Unfortunately SET OPTION NAMING can only be specified within embedded SQL but is not allowed in an SQL function, SQL procedure, or SQL trigger.

          Because we are working with System i applications, i.e. using a library list and group profiles, I develop all my SQL routines solely with System i Navigator.

          May be we need to open an request at Rational to allow SQL AND System Naming

      • krmilligan
        446 Posts

        Re: Rational -- UDFs &#38; Stored Procedures

        ‏2012-10-16T15:36:58Z  in response to JamesStrick
        What DB2 for i issues are you encountering with Data Studio client?