#6428: For iSeries DB2, scripting an existing view in the Query Analyzer is missing the body of the view.

hmalsch reported 2012-02-10T16:50:38Z · last modified 2012-02-14T15:11:16Z

For iSeries DB2, scripting an existing view in the Query Analyzer is missing the body of the view.

Dev	ivangron
QA	NielsGron

Priority	Major
Complexity	Unknown

Component	Editor - Debugger for DB2
Version	10.0

I'm running ADS 10.0.7 on Windows 7. I've also tested the following on another computer running ADS version 9.xx with the same results. I'm connected to an iSeries DB2 database.

I created a view using Aquadata as follows:

CREATE VIEW vwErrors( ID, MESSAGE_ID, VALIDATOR_NAME, TYPE, MESSAGE, DETAILS, FIELD_NAME, RT, TT, DAY_HEADER_ID)
AS
select ID, MESSAGE_ID, VALIDATOR_NAME, TYPE, MESSAGE, DETAILS, FIELD_NAME, REPORT_TYPE as RT, TRANSACTION_TYPE as TT, DAY_HEADER_ID
from VALERRORS

The view works fine, but when I right click on the view in Server Explorer and select "Script Object o Window As..." - "Create", I get this:
-------------------------------------
DROP VIEW CRMINT.VWERRORS
GO
CREATE VIEW CRMINT.VWERRORS ( ID, MESSAGE_ID, VALIDATOR_NAME FOR VALID00001, TYPE, MESSAGE, DETAILS, FIELD_NAME, RT, TT, DAY_HEADER_ID FOR DAY_H00001 )
AS

...a bunch of GRANT statements here...
-------------------------------------

The body is completely missing. The same happens if I go directly to "Alter View"

Harry

3 attachments

sysview results

2012-02-10T22:07:08Z

6 KB
datastudio.log

2012-02-11T17:22:51Z

7 KB
startup.log

2012-02-11T17:35:49Z

14 KB

All Comments (6) Change History

ivangron 2012-02-10T22:47:14Z · (edited)

ads uses the following query to extract view information.

1.) would it be possible to run this query to see if the data is missing from the catalog tables?

if it is missing, why?

2.) without access to an iSeries db I can only dry test the code, I am not sure why we are using qualifying QSYS2.SYSVIEWS in the query when the rest of the tables/views are not schema qualified.

online docs for this table: http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzcatsysviews.htm

SELECT

v.TABLE_SCHEMA,

v.TABLE_NAME,

v.VIEW_DEFINITION,

v.CHECK_OPTION,

c.COLUMN_NAME,

c.SYSTEM_COLUMN_NAME,

v.SYSTEM_VIEW_NAME,

c.LONG_COMMENT,

c.COLUMN_HEADING,

t.LONG_COMMENT,

t.TABLE_TEXT

FROM

QSYS2.SYSVIEWS v

LEFT JOIN SYSCOLUMNS c ON (v.TABLE_NAME = c.TABLE_NAME AND v.VIEW_OWNER = c.TABLE_OWNER)

LEFT JOIN SYSTABLES t ON (v.TABLE_NAME = t.TABLE_NAME AND v.VIEW_OWNER = t.TABLE_OWNER)

WHERE

v.TABLE_SCHEMA = 'view_schema'

AND v.TABLE_NAME = 'view_name'

hmalsch 2012-02-10T20:36:07Z

Well, I think you've found the problem. I ran the SQL you supplied and it returned:

>[Error] Script lines: 6-24 -------------------------
[SQL0551] Not authorized to object SYSCOLUMNS in CRMINT type *FILE.

[Executed: 2/10/2012 2:33:41 PM] [Execution: 0ms]
---------------------------------------------------------------------------------------------------------

I'll check with our iSeries admin on this and get back to you.

hmalsch 2012-02-10T22:08:31Z

Apparently the permission's issue has been fixed. At least with the tables in question. I've attached the results of the query you sent me (viewDefProblem.xls).

My original problem still exists. I used IBM's iNavigator to script the view and it works.

NielsGron 2012-02-10T23:50:19Z

Can you script, then send us your application logs from <USER_HOME>\.datstudio\logs\*? You can attach or email to architect@aquafold.com.

hmalsch 2012-02-11T17:42:29Z · (edited)

I scripted the view twice, using two different log in's, and attached datastudio.log and startup.log. To keep it simple I removed everything from prior to today. I see the error in the log, but don't understand it. I'd appreciate any help. I’ll also be reviewing this with our iSeries admin on Monday.

Thanks,

Harry

NielsGron 2012-02-13T22:32:47Z

syscolumns needed fully qualified reference.

Search Tips

Aqua Data Studio / nhilam

For iSeries DB2, scripting an existing view in the Query Analyzer is missing the body of the view.

3 attachments

Issue #6428

Completion

Search Tips

Aqua Data Studio / nhilam

Title

For iSeries DB2, scripting an existing view in the Query Analyzer is missing the body of the view.

3 attachments

Issue #6428

Completion