1

Hello guy I create a function in DB2 UDB for AS/400 version 07.01.0000 V7R1m0

I use a windows with dbvisualizer to connect the server.

My function is...

CREATE FUNCTION JVAOBJ.BNOWPAPOL(POL VARCHAR(10)) RETURNS DECIMAL(7,7) LANGUAGE SQL NOT
    DETERMINISTIC READS SQL DATA
    RETURN
    (
        SELECT
            CASE
                WHEN NUM IN (1,2)
                THEN 0.3
                ELSE 0.19698
            END AS VALOR
        FROM
            LMDDTA.VERT240                   
        WHERE
            POLLIFE = POL )

It return 0.3 or 0.19698 depending of POL param

To do it I delete DROP FUNCTION JVAOBJ.BNOWPAPOL and run CREATE until work well.

My problem is I can not see the actual code of the function in dbvisualizer I cant see the function created

dbvisualiser screnshoot

How I can see the actual code?

Note: The server administrator has access to the console as400 (yes, that black screen with green letters or orange letters, which I have not much knowledge) maybe, I can see it from here.

Note 2: I use jt400 driver to connect.

Buck Calabro
  • 7,558
  • 22
  • 25
jasilva
  • 730
  • 3
  • 17
  • 45
  • You show an image from dbvis, but it's not clear if you explored it. What shows under the JVAOBJ-> Procedures node? – user2338816 May 06 '15 at 20:05
  • @user2338816 This expanded, nothing – jasilva May 06 '15 at 20:15
  • 1
    To ensure the SQL source code is valid world-wide [such that anyone from a country who might assist, but for which their decimal separator is the comma instead of the period], for any number preceded by a comma as a separator, always include a preceding blank for the next\separate number. For example, code 'DECIMAL(7, 7)' instead of 'DECIMAL(7,7)' and 'IN (1, 2)' instead of 'IN (1,2)' Extremely important if the source code is _shared_ amongst developers, rather than just the compiled object(s) being shared. – CRPence May 06 '15 at 20:59

2 Answers2

2

Try IBM i Navigator for web. If it is configured on your machine, you can reach it through this URL: https://your.ibm.i:2005/ibm/console/login.do?action=secure

If it is not configured, then perhaps you can ask the admin to install the Windows client. It is part of Client Access for Windows and is called IBM i Navigator for Windows.

In either case, use the navigation tree to go to Databases > machine > Schemas > JVAOBJ > Functions. Right click your function > Definition and then choose Routine Body

EDIT Add SYSROUTINE Another way to see the routine body is via the DB2 catalog table SYSROUTINE. select specific_schema, specific_name, routine_definition from sysroutine

Buck Calabro
  • 7,558
  • 22
  • 25
  • I check with sysadmin in `IBM i Navigator for Windows` tool in tree go to `Databases - > machine > Schemas -> QGPL` And nothing shows, also JVAOBJ item in not in tree. way to check it from the `Client Access for Windows` ? – jasilva May 06 '15 at 20:27
  • Under SCHEMAS there should be many entries, not just QGPL. Have the admin add JVAOBJ to the list and you will be able to see the contents of that schema. – Buck Calabro May 06 '15 at 20:33
  • Right click Schemas and then choose 'Select schemas to display' – Buck Calabro May 06 '15 at 20:45
1

I know IBM i Navigator its a great tool, but you need some amount of knowledge to master it.

The easiest way is to query the SQL system tables using dbvisualizer

SELECT * FROM qsys2/sysfuncs 
WHERE (SPECIFIC_SCHEMA, SPECIFIC_NAME) = ('JVAOBJ', 'BNOWPAPOL')
Jairo R. Flores
  • 724
  • 4
  • 11