CODEWITHSUNDEEP
databaseibm-midrange
Totty.js
Totty.js

Reputation: 15841

How to extract my table_names+descriptions from my "database navigator" from IBM?

I have a my database navigator that contains a lot of tables with descriptions. I need to search within descriptions in order to find the table I need, but in the database navigator from ibm this feature is not available. Because of this I want to export all data to a excel sheet. But how?

Upvotes: 0

Views: 980

Answers (2)

U.G.
U.G.

Reputation: 1

You can also do it the old fashioned way, if you have access to green screen and the command line.

Use WRKOBJPDM LIB(youiSerieaLibraryName) OBJTYPE(*FILE)

then use F17 Subset (Shift and F5 ) and enter your search criteria for the file description next to TEXT

Upvotes: 0

dmc
dmc

Reputation: 2684

This SQL statement will give you the schema (library), table name, and description for every table you have access to.

SELECT TABLE_SCHEMA, NAME, TABLE_TEXT   
FROM QSYS2.SYSTABLES

You can get it into Excel in a few ways. I am not a big fan of iSeries Navigator, so I tend to use Microsoft Query to get my data into Excel.

Here's how I do it:

  1. Go to the Data ribbon (I'm on 2007)
  2. Select "From Other Sources"
  3. Select "From Microsoft Query"
  4. choose your data source and click OK
  5. Cancel out of the Query Wizard
  6. Answer "Yes" to the question "Do you want to continue editing this query in Microsoft Query?"
  7. Close out of the "Add Tables" dialog.
  8. Hit the SQL button on the tool bar (or choose "SQL..." from the "View" menu).
  9. Paste in the SQL statement and click OK.
  10. If "SQL Query can't be represented graphically. Continue anyway?" comes up, click OK.
  11. Choose "Return data to Microsoft Office Excel" from the "File" menu.

Yes, it's a klunky process, but it works. The only downside is that Microsoft Query runs the query to show it to you, the Excel runs it again to pull the data. That might not always be a good idea if it's an expensive query to run. I wouldn't worry about it in this case.

Upvotes: 1

Related Questions