Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Stored procedure not generating DDL on IBM i DB2 platforms #1857

Closed
xantari opened this issue Jul 7, 2017 · 12 comments

Comments

6 participants
@xantari
Copy link

commented Jul 7, 2017

When trying to generate the DDL on IBM i DB2 platforms we get this:

-- Source code not available;

@serge-rider

This comment has been minimized.

Copy link
Member

commented Jul 28, 2017

This is not supported yet.
IBM i DB2 driver uses plain JDBC for metadata reading. JDBC API doesn't provide stored procedures source code.

@JMarkMurphy

This comment has been minimized.

Copy link

commented Apr 25, 2019

Please re-open this, Stored procedure source code is available from the system catalog on DB2 for i. The catalog view you need to interrogate is QSYS2.SYSROUTINES. Documentation is here

@serge-rider serge-rider reopened this Apr 30, 2019

@serge-rider serge-rider added this to the 6.0.5 milestone Apr 30, 2019

@xantari

This comment has been minimized.

Copy link
Author

commented Apr 30, 2019

I can beta test for you when this is ready

@tati-kru tati-kru modified the milestones: 6.0.5, 6.1.0 May 29, 2019

@serge-rider serge-rider modified the milestones: 6.1.0, 6.1.1 Jun 10, 2019

@tati-kru tati-kru modified the milestones: 6.1.1, 6.1.2 Jun 10, 2019

@tati-kru tati-kru added this to Backlog in DBeaver project Jul 3, 2019

@serge-rider serge-rider moved this from Backlog to In Progress in DBeaver project Jul 5, 2019

serge-rider added a commit that referenced this issue Jul 5, 2019

serge-rider added a commit that referenced this issue Jul 5, 2019

@serge-rider serge-rider moved this from In Progress to Ready for QA in DBeaver project Jul 5, 2019

@serge-rider

This comment has been minimized.

Copy link
Member

commented Jul 5, 2019

@xantari Please test in Early Access version (https://dbeaver.io/files/ea)

@JMarkMurphy

This comment has been minimized.

Copy link

commented Jul 5, 2019

Still see -- Source code not available

image

Here is my configuration:
*** Date: Friday, July 5, 2019 at 8:43:09 AM Eastern Daylight Time

*** Platform Details:

*** System properties:

serge-rider added a commit that referenced this issue Jul 5, 2019

@serge-rider

This comment has been minimized.

Copy link
Member

commented Jul 5, 2019

Thanks for testing. Could you try it one more time? (new EA was just published)

@serge-rider serge-rider closed this Jul 5, 2019

DBeaver project automation moved this from Ready for QA to Done Jul 5, 2019

@JMarkMurphy

This comment has been minimized.

Copy link

commented Jul 5, 2019

Getting closer. One problem, and a comment.

Problem first: you are getting user defined functions, but not stored procedures:
image

In the query result of sysroutines we see a bunch of functions (some of these are table functions) and a single lonely procedure. But the procedure does not appear in the Database Navigator.

And now the comment: you will notice that in the ROUTINE_BODY column, some say SQL, and others say EXTERNAL. DB2 for i allows registering functions, procedures, and triggers that are not defined in SQL. These show as EXTERNAL. The ones with SQL show source now. The others still say -- Source code not available. It would be nice if instead of that it would indicate that the routine is externally defined, and also provide the external name, language, and parameter style. You still won't be able to get the source from these, but it would be nice to be able to find the program that is registered.

@serge-rider

This comment has been minimized.

Copy link
Member

commented Jul 5, 2019

Good (more or less). Thanks for checking this!

Procedure list is provided by jt400 driver (currently DBeaver doesn't make direct selects from SYSROUTINES). Generally we can change this behavior but it is not very easy for me to write this code in blind mode.
Could you write a SQL query which will return all procedures/functions with all needed properties (including type and body) so I could use it in procedure reading routine? Input param is schema name.

Procedure object may have any number of properties (if they make sense in UI). Please write short description of such properties.

@emuggie

This comment has been minimized.

Copy link

commented Jul 8, 2019

DB2 for i 7.2 user.
Commented Column --Column meanings (DataType) : confirmed values.
Please correct me if there's wrong info.

--Query
SELECT
SPECIFIC_SCHEMA --SYSTEM SCHEMA NAME(VARCHAR)
, SPECIFIC_NAME --SYSTEM NAME(VARCHAR)
, ROUTINE_SCHEMA --SCHEMA(VARCHAR)
, ROUTINE_NAME --ROUTINE NAME(VARCHAR)
, ROUTINE_TYPE -- TYPE(VARCHAR) : FUNCTION, PROCEDURE
, ROUTINE_CREATED --CREATED TIME(TIMESTAMP)
, ROUTINE_DEFINER -- USER WHOM CREATED ROUTINE(VARCHAR)
, ROUTINE_BODY -- ROUTINE(VARCHAR) : SQL, EXTERNAL
, EXTERNAL_NAME --(VARCHAR)
, EXTERNAL_LANGUAGE --(VARCHAR)
, PARAMETER_STYLE --(VARCHAR)
, IS_DETERMINISTIC -- IS DETERMINISTIC(VARCHAR) : YES, NO
, SQL_DATA_ACCESS -- IS MODIFING DATA(VARCHAR) : READS, MODIFIES
, SQL_PATH --(VARCHAR)
, PARM_SIGNATURE --(VARCHAR)
, NUMBER_OF_RESULTS --(SMALLINT)
, MAX_DYNAMIC_RESULT_SETS --(SMALLINT)
, IN_PARMS -- NUBMER OF IN PARAMETERS(SMALLINT)
, OUT_PARMS -- NUMBER OF OUT PARAMETERS(SMALLINT)
, INOUT_PARMS --NUMBER OF INOUT PARAMETERS(SMALLINT)
, PARSE_TREE --(VARCHAR)
, PARM_ARRAY --(BLOB)
, LONG_COMMENT --ROUTINE COMMENT(VARCHAR)
, ROUTINE_DEFINITION --SQL SCRIPT FOR ROUTINE(DBCLOB)
, FUNCTION_ORIGIN --(CHAR)
, FUNCTION_TYPE --(CHAR)
, EXTERNAL_ACTION --(CHAR)
, IS_NULL_CALL --(VARCHAR)YES,NO,[NULL]
, SCRATCH_PAD --(INTEGER)[NULL]
, FINAL_CALL --(VARCHAR)YES,NO,[NULL]
, PARALLELIZABLE --(VARCHAR)YES,NO,[NULL]
, "DBINFO" --(VARCHAR)YES,NO,[NULL]
, SOURCE_SPECIFIC_SCHEMA --(VARCHAR)
, SOURCE_SPECIFIC_NAME --(VARCHAR)
, IS_USER_DEFINED_CAST --(VARCHAR)YES,NO,[NULL]
, "CARDINALITY" --(BIGINT)[NULL]
, "FENCED" --(VARCHAR)YES,NO,[NULL]
, COMMIT_ON_RETURN --(VARCHAR)YES,NO,[NULL]
, IASP_NUMBER --(SMALL INT)
, NEW_SAVEPOINT_LEVEL --(VARCHAR)YES,NO
, LAST_ALTERED --(TIMESTAMP)[NULL]
, DEBUG_MODE --(CHAR)0,2
, DEBUG_DATA --(CLOB)
, ROUNDING_MODE --(CHAR)E
, ROUTINE_TEXT --(NVARCHAR)
, SECURE --(CHAR)N
, ROUTINE_ENVIRONMENT --(BLOB)
, ROUTINE_DEFAULT_QDT --(BLOB)
FROM
QSYS2.SYSROUTINES

@serge-rider

This comment has been minimized.

Copy link
Member

commented Jul 8, 2019

@emuggie Thanks. This feature will be tracked in #6241

@JMarkMurphy

This comment has been minimized.

Copy link

commented Jul 9, 2019

@Condor70

This comment has been minimized.

Copy link

commented Jul 11, 2019

There is an error in the 6.1.2 implementation which results in:

/* ERROR WHILE READING SOURCE:

SQL Error [42704]: [SQL0204] SYSROUTINES in MYSCHEMA type *FILE is niet gevonden.
*/

Line 55 in org.jkiss.dbeaver.ext.db2.i.model.DB2IMetaModel is:
SELECT ROUTINE_DEFINITION FROM SYSROUTINES
but should be:
SELECT ROUTINE_DEFINITION FROM QSYS2.SYSROUTINES

(reference)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.