Solved

export view definitions from as400 db2

Posted on 2014-04-18
21
2,024 Views
Last Modified: 2014-04-28
I need to export the view definitions for a couple libraries on the AS400 (iSeries v5r4) from sql server 2000.  I have already imported the tables, but I really don't want to do  manually selecting each view in iSeries Navigator and exporting the sql.

can anyone show me the sql to do this?
0
Comment
Question by:dhenderson12
  • 10
  • 6
  • 3
  • +1
21 Comments
 
LVL 18

Expert Comment

by:daveslash
ID: 40008945
This should do it:

SELECT table_schema,
       TABLE_NAME,
       VIEW_DEFINITION
  FROM sysviews

Open in new window


HTH,
DaveSlash
0
 

Author Comment

by:dhenderson12
ID: 40008973
I cannot run that sql statement from sql server.  AS400 is linked server.
0
 

Author Comment

by:dhenderson12
ID: 40009006
I actually need the sql that created the view in a particular library:  If I have a library "myLibrary" and a table name "myTable" I can query the table.  Now I need to get the actual sql that created the view in the library "myLibrary".  How can I do that?
0
 
LVL 18

Expert Comment

by:daveslash
ID: 40009025
To my knowledge, there's no way to do that from SQL Server. You'll have to get some kind of direct access to the AS/400. (I recommend the GUI ... System i Navigator).
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40009566
You can use OPENQUERY and INFORMATION_SCHEMA.VIEWS, something like this:

SELECT *
FROM OPENQUERY(as400_linked_server_name_goes_here,
    'SELECT * FROM information_schema.views /*WHERE TABLE_NAME LIKE ''pattern%''*/ ')

Change the subquery to return only the specific column(s) you need.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40010412
It's not necessary to select "each view in iSeries Navigator". They can all be selected at once and generated in a single operation.
I cannot run that sql statement from sql server.
So don't run it from SQL Server. Run it in iSeries Navigator.

Tom
0
 

Author Comment

by:dhenderson12
ID: 40010507
ScottPletcher,
The open query works (thanks!) but how do I get the views for a specific library?  they are all comming from QSYS2 library ...
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40012133
SELECT * FROM information_schema.views where TABLE_SCHEMA in( 'mylib1', 'mylib2', ...)

Open in new window

Use OPENQUERY to select the views from the information schema by using a WHERE clause. Select one at a time or list them in an IN() list.

Tom
0
 

Author Comment

by:dhenderson12
ID: 40012394
tliotta,
If I specify the library name, nothing is returned.  Here is my query:

SELECT *
FROM OPENQUERY(as400,
    'SELECT * FROM information_schema.views WHERE TABLE_SCHEMA in (''MK3'') ')

MK3 is a library with several tables and views.
0
 

Author Comment

by:dhenderson12
ID: 40012438
btw, I just noticed that the statement will not return values with a "where" clause, even in iseries navigator.  

If I just run the "select * from information_schema.views" query it returns all views in the qsys2 library.

If I add a the "where" clause, it returns no results, except in the case of QSYS2.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40012517
The linked server user must not have permissions to that library/view.

Try SYSIBM2, which is a more "open" view that shows everything regardless of permissions (gotta love that "security" eh? :-) ):


SELECT *
FROM OPENQUERY(as400,
    'SELECT * FROM sysibm2.tables WHERE TABLE_SCHEMA in (''MK3'') ')
0
 

Author Comment

by:dhenderson12
ID: 40012821
that returned the following error:
TABLES in SYSIBM2 type *FILE was not found.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40012950
D'OH, sorry, it should be just "SYSIBM":


SELECT *
FROM OPENQUERY(as400,
    'SELECT * FROM SYSIBM.TABLES WHERE TABLE_SCHEMA in (''MK3'') ')
0
 

Author Comment

by:dhenderson12
ID: 40013002
although that query lists the views by library (thanks!), it does not include the view definition for the views;  the view definition is what I need.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40013948
If permissions are restricting access through INFORMATION_SCHEMA, it might restrict access through other database catalog avenues. But because INFORMATION_SCHEMA has become SYSIBM, another change is also needed (at a minimum):
SELECT *
FROM OPENQUERY(as400,
    'SELECT * FROM SYSIBM.VIEWS WHERE TABLE_SCHEMA in (''MK3'') ')

Open in new window

Selection should be through VIEWS rather than TABLES.

Tom
0
 

Author Comment

by:dhenderson12
ID: 40024697
sorry for the delay, folks.  had some issues posting back on this question.

anyway, the open query using VIEWS does not return the view definition by library.
it returns the views from the sysibm and qsys2 libraries only.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40025271
it returns the views from the sysibm and qsys2 libraries only.
It's not clear what that means. Since SYSIBM and/or QSYS2 is where the view definitions are stored, that's where they should come from. By including a qualifying phrase "WHERE TABLE_SCHEMA in ('MK3')", only the definitions of views that are defined for library 'MK3' would be pulled from the catalog that is presented in SYSIBM and/or QSYS2.

If 'MK3' is replaced with a different library name (e.g., 'MK2' or 'AB4'), then a different set of results will be returned. The different set will still be retrieved from SYSIBM and/or QSYS2.

Tom
0
 

Author Comment

by:dhenderson12
ID: 40025285
I understand what you're saying, but isn't working ... when I specify a library other than qsys2 or sysibm no records are returned.  Perhaps you have access to an as400 to test your query on or let me know if it's a permissions thing.
0
 
LVL 27

Accepted Solution

by:
tliotta earned 500 total points
ID: 40026465
Okay, and I'm mostly clear now. Of course, I can't run the same query; but similar queries work fine for me. In my case, permissions aren't a problem.

However, there is an alternative problem. There might not be any "VIEW"s. That is, SQL VIEWs are implemented as a kind of subclass of native *FILE object types. In particular, they are a form native Logical Files (LFs). The native database system has PFs (Physical Files) that are what SQL TABLEs are based on, and LFs that SQL VIEWs are based on.

PFs and LFs are created with a data modelling language known as Data Description Specifications (DDS).

But the SQL database catalog won't include most or many LFs. They aren't SQL objects because SQL didn't create them. There was no SQL CREATE VIEW statement that was ever run, so none was ever cataloged.

Two ways that I use in such cases:

1. Use iSeries Navigator to select all LFs (listed under 'Views') in a schema and 'Generate SQL'.
2. Use Data Studio to do essentially the same thing.

IMO, iSeries Navigator is often easier simply because of fewer options to learn/navigate. (It's explicitly designed for DB2 on i rather than for DB2 LUW and z/OS, with DB2 on i added as almost an afterthought.) Just select all of them, right-click and choose 'Generate SQL'. I almost always open the result in 'Run SQL Scripts' because I commonly do some editing before saving, but the whole script of all generated VIEWs can be saved directly to a PC file if you prefer.

If you already use Data Studio, though, the reverse might be true for you.

By using a 'Generate SQL' option in either product, you'll get a copy of what the corresponding SQL DDL would be.

Tom
0
 

Author Comment

by:dhenderson12
ID: 40027015
Yeah, that's what I was afraid of.  I was trying to avoid Navigator because it is a manual operation to select the views and export them and I have about 200 libraries. :(

I have never  heard of or worked with Data Studio.

Thanks for your help.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40028712
Unfortunately, if these aren't actual SQL objects, it's going to be a "manual" operation.

...Unless you're comfortable coding to APIs. You can always call the Generate Data Definition Language (QSQGNDDL) API in a program that processes a list of libraries and lists of LFs in each library. AFAIK, that's how tools such as iNav and Data Studio do it.

Tom
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now