DB2 Generate DDL (2.3.6)

Note: please leave bug reports and feature requests on GitHub

DB2 Generate DDL (2.3.6)

Postby Harrie » Tue Jan 28, 2014 9:46 pm

Generate DDL (DB2) works great with Win7/Dbeaver236/DB2-10.5, but it doesn't with AIX6.1/Dbeaver236/DB2-9.7fp7 ( screen returns blank ).
There seems to be a popup screen which I haven't been able to "catch".
I'm dbadm in both environments.
Any idea ?

Dbeaver is an excellent tool by the way !
Harrie
 
Posts: 1
Joined: Tue Jan 28, 2014 9:39 pm

Re: DB2 Generate DDL (2.3.6)

Postby titou10 » Wed Jan 29, 2014 1:13 pm

Thanks for the compliment.

DBeaver uses an "undocumented" stored proc to generate table DDL: SYSPROC.DB2LK_GENERATE_DDL(). This one is used internally by the "db2look" command
This is the best way to generate an exact and "perfect" DDL, corresponding to what db2look gives. The drawback is that you need to have access to this SP and its dependencies, including the SYSTOOLS.DB2LOOK_INFO_V view

The SQL executed to generate DDL is actually:
call SYSPROC.DB2LK_GENERATE_DDL("-e -td <delimiter> -t <fully qualified table name>",?)

Maybe this SP or its dependencies do not exist or act differently of on your server (I test with DB2 Win/v10.5 and DB2 AIX64/9.7+10.1 too..)

Could you verify if you have something in the error logs or in the query manager view while you try to display DDL of a table . Also please check the dbeaver error.log (in your <user home>/.dbeaver/.metadata/dbeaver-debug.log)
To display those views, in the "Window" menu
- check "Query manager"
- chek "Error Log"

Please send the result to me : titou10.titou10@gmail.com

Thanks

Denis
titou10
 
Posts: 37
Joined: Fri Aug 30, 2013 1:52 am

Re: DB2 Generate DDL (2.3.6)

Postby blakeice » Mon Jun 23, 2014 4:06 pm

I am also getting this error message even when connecting as the instance owner.

DB2 9.7 FP9 Linux
dbeaver 2.4.2

These are lines from the .log file:

Code: Select all
!ENTRY org.jkiss.dbeaver.core 4 0 2014-06-23 10:51:30.394
!MESSAGE org.jkiss.dbeaver.DBException: SQL Error [38553]: Routine "*RATE_DDL" (specific name "") has returned an error SQLSTATE with diagnostic text "SQL0551 Reason code or token: DBUSER,CREATE TABLE,SYSTOOLSP".. SQLCODE=-443, SQLSTATE=38553, DRIVER=4.16.53
!STACK 0
org.jkiss.dbeaver.DBException: SQL Error [38553]: Routine "*RATE_DDL" (specific name "") has returned an error SQLSTATE with diagnostic text "SQL0551 Reason code or token: DBUSER,CREATE TABLE,SYSTOOLSP".. SQLCODE=-443, SQLSTATE=38553, DRIVER=4.16.53
   at org.jkiss.dbeaver.ext.db2.DB2Utils.generateDDLforTable(DB2Utils.java:218)
   at org.jkiss.dbeaver.ext.db2.model.DB2Table.getSourceDeclaration(DB2Table.java:200)
   at org.jkiss.dbeaver.ext.db2.editors.DB2SourceDeclarationEditor.getSourceText(DB2SourceDeclarationEditor.java:36)
   at org.jkiss.dbeaver.ui.editors.sql.SQLEditorNested$ObjectDocumentProvider$1.run(SQLEditorNested.java:212)
   at org.jkiss.dbeaver.core.DBeaverUI$3.run(DBeaverUI.java:205)
   at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
Caused by: com.ibm.db2.jcc.am.SqlException: Routine "*RATE_DDL" (specific name "") has returned an error SQLSTATE with diagnostic text "SQL0551 Reason code or token: DBUSER,CREATE TABLE,SYSTOOLSP".. SQLCODE=-443, SQLSTATE=38553, DRIVER=4.16.53
   at com.ibm.db2.jcc.am.fd.a(fd.java:744)
   at com.ibm.db2.jcc.am.fd.a(fd.java:60)
   at com.ibm.db2.jcc.am.fd.a(fd.java:127)
   at com.ibm.db2.jcc.am.to.b(to.java:2422)
   at com.ibm.db2.jcc.am.to.c(to.java:2405)
   at com.ibm.db2.jcc.t4.ab.l(ab.java:408)
   at com.ibm.db2.jcc.t4.ab.f(ab.java:98)
   at com.ibm.db2.jcc.t4.o.e(o.java:81)
   at com.ibm.db2.jcc.t4.tb.k(tb.java:170)
   at com.ibm.db2.jcc.am.to.nb(to.java:2371)
   at com.ibm.db2.jcc.am.uo.b(uo.java:4533)
   at com.ibm.db2.jcc.am.CallableStatement.kc(CallableStatement.java:206)
   at com.ibm.db2.jcc.am.CallableStatement.executeUpdate(CallableStatement.java:181)
   at com.ibm.db2.jcc.am.kf.executeUpdate(kf.java:78)
   at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCPreparedStatementImpl.executeUpdate(JDBCPreparedStatementImpl.java:122)
   at org.jkiss.dbeaver.ext.db2.DB2Utils.generateDDLforTable(DB2Utils.java:169)
   ... 5 more
!SUBENTRY 1 org.jkiss.dbeaver.core 4 0 2014-06-23 10:51:30.394
!MESSAGE SQL Error [38553]: Routine "*RATE_DDL" (specific name "") has returned an error SQLSTATE with diagnostic text "SQL0551 Reason code or token: DBUSER,CREATE TABLE,SYSTOOLSP".. SQLCODE=-443, SQLSTATE=38553, DRIVER=4.16.53
!SUBENTRY 2 org.jkiss.dbeaver.core 4 0 2014-06-23 10:51:30.394
!MESSAGE Routine "*RATE_DDL" (specific name "") has returned an error SQLSTATE with diagnostic text "SQL0551 Reason code or token: DBUSER,CREATE TABLE,SYSTOOLSP".. SQLCODE=-443, SQLSTATE=38553, DRIVER=4.16.53


When I run it against an AIX 9.7 FP9, I get the following in the .log file:

Code: Select all
!ENTRY org.jkiss.dbeaver.core 4 0 2014-06-23 11:01:42.956
!MESSAGE org.jkiss.dbeaver.DBException: SQL Error [38553]: Routine "DB2LK_GENERATE_DDL" (specific name "*TE_DDL") has returned an error SQLSTATE with diagnostic text "SQL0031 Reason code or token: /db2lkfun.bnd".. SQLCODE=-443, SQLSTATE=38553, DRIVER=4.16.53
!STACK 0
org.jkiss.dbeaver.DBException: SQL Error [38553]: Routine "DB2LK_GENERATE_DDL" (specific name "*TE_DDL") has returned an error SQLSTATE with diagnostic text "SQL0031 Reason code or token: /db2lkfun.bnd".. SQLCODE=-443, SQLSTATE=38553, DRIVER=4.16.53
   at org.jkiss.dbeaver.ext.db2.DB2Utils.generateDDLforTable(DB2Utils.java:218)
   at org.jkiss.dbeaver.ext.db2.model.DB2Table.getSourceDeclaration(DB2Table.java:200)
   at org.jkiss.dbeaver.ext.db2.editors.DB2SourceDeclarationEditor.getSourceText(DB2SourceDeclarationEditor.java:36)
   at org.jkiss.dbeaver.ui.editors.sql.SQLEditorNested$ObjectDocumentProvider$1.run(SQLEditorNested.java:212)
   at org.jkiss.dbeaver.core.DBeaverUI$3.run(DBeaverUI.java:205)
   at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
Caused by: com.ibm.db2.jcc.am.SqlException: Routine "DB2LK_GENERATE_DDL" (specific name "*TE_DDL") has returned an error SQLSTATE with diagnostic text "SQL0031 Reason code or token: /db2lkfun.bnd".. SQLCODE=-443, SQLSTATE=38553, DRIVER=4.16.53
   at com.ibm.db2.jcc.am.fd.a(fd.java:744)
   at com.ibm.db2.jcc.am.fd.a(fd.java:60)
   at com.ibm.db2.jcc.am.fd.a(fd.java:127)
   at com.ibm.db2.jcc.am.to.b(to.java:2422)
   at com.ibm.db2.jcc.am.to.c(to.java:2405)
   at com.ibm.db2.jcc.t4.ab.l(ab.java:408)
   at com.ibm.db2.jcc.t4.ab.f(ab.java:98)
   at com.ibm.db2.jcc.t4.o.e(o.java:81)
   at com.ibm.db2.jcc.t4.tb.k(tb.java:170)
   at com.ibm.db2.jcc.am.to.nb(to.java:2371)
   at com.ibm.db2.jcc.am.uo.b(uo.java:4533)
   at com.ibm.db2.jcc.am.CallableStatement.kc(CallableStatement.java:206)
   at com.ibm.db2.jcc.am.CallableStatement.executeUpdate(CallableStatement.java:181)
   at com.ibm.db2.jcc.am.kf.executeUpdate(kf.java:78)
   at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCPreparedStatementImpl.executeUpdate(JDBCPreparedStatementImpl.java:122)
   at org.jkiss.dbeaver.ext.db2.DB2Utils.generateDDLforTable(DB2Utils.java:169)
   ... 5 more
!SUBENTRY 1 org.jkiss.dbeaver.core 4 0 2014-06-23 11:01:42.956
!MESSAGE SQL Error [38553]: Routine "DB2LK_GENERATE_DDL" (specific name "*TE_DDL") has returned an error SQLSTATE with diagnostic text "SQL0031 Reason code or token: /db2lkfun.bnd".. SQLCODE=-443, SQLSTATE=38553, DRIVER=4.16.53
!SUBENTRY 2 org.jkiss.dbeaver.core 4 0 2014-06-23 11:01:42.956
!MESSAGE Routine "DB2LK_GENERATE_DDL" (specific name "*TE_DDL") has returned an error SQLSTATE with diagnostic text "SQL0031 Reason code or token: /db2lkfun.bnd".. SQLCODE=-443, SQLSTATE=38553, DRIVER=4.16.53


I am not sure why they are different.
blakeice
 
Posts: 1
Joined: Mon Jun 23, 2014 3:53 pm

Re: DB2 Generate DDL (2.3.6)

Postby titou10 » Mon Jun 23, 2014 8:02 pm

Hi,
It seems that you are not authorized to create the system tables or some internal object needed by the executuing of the db2look internal stored proc
Are you sure you are connected as the instance owner? could you try to run the equivalent db2look command with this user and the same table?
Maybe you could also check what is is written in this post : http://dbeaver.jkiss.org/forum/viewtopic.php?f=2&t=1151&p=2571&hilit=DB2LK_GENERATE_DDL#p2571
Denis
titou10
 
Posts: 37
Joined: Fri Aug 30, 2013 1:52 am

Re: DB2 Generate DDL (2.3.6)

Postby asouza22 » Tue May 17, 2016 1:43 pm

Hello!

I was getting an error similar to this. I fixed it GRANTing the rights privilegies on DB2LOOK_TOKEN sequence, DB2LOOK_INFO_V view and DB2LOOK_INFO table in the SYSTOOLS schema.
Maybe it can help!

Regards!
asouza22
 
Posts: 1
Joined: Tue May 17, 2016 1:36 pm

Re: DB2 Generate DDL (2.3.6)

Postby Cris70 » Thu May 19, 2016 7:32 am

I am having a similar error, but it doesn't seem related to grants:
Code: Select all
Errore SQL [38553]: Routine "*RATE_DDL" (specific name "*RATE_DDL") has returned an error SQLSTATE with diagnostic text "SQL0727 Reason code or token: 2,-206,42703,OBJ_SUBTYPE     ".. SQLCODE=-443, SQLSTATE=38553, DRIVER=4.14.146
  com.ibm.db2.jcc.am.SqlException: Routine "*RATE_DDL" (specific name "*RATE_DDL") has returned an error SQLSTATE with diagnostic text "SQL0727 Reason code or token: 2,-206,42703,OBJ_SUBTYPE     ".. SQLCODE=-443, SQLSTATE=38553, DRIVER=4.14.146

Instead, it seems a column is missing.
This is with DB2 9.7 on AIX (DB2/AIX64 [SQL0907B]) with DBeaver 2.3.7.

Help, please

Cris
Cris70
 
Posts: 4
Joined: Tue May 10, 2016 8:22 am


Return to Support



Who is online

Users browsing this forum: No registered users and 27 guests