2

I'm working in a IBM environment, specifically with AS400 machines and DB2 databases.
My next task is converting every possible file from PF and LF, to SQL (for instance, CREATE TABLE... and CREATE VIEW...).

Are there cases when I can't do that?
I know that for multiple record format file you can't, is it true?

LppEdd
  • 20,274
  • 11
  • 84
  • 139
  • FWiW if there is no specific known value from making such a change from DDS to SQL, having done so would be _change for the sake of change_; introduction of change for no specific purpose is more likely to experience problems than for not having made\introduced the change. There are some who might make wild claims about the benefits from such blind changes, but unlike such _snake oil salesmen_, there are other who know better from stupid -- if only from experience. – CRPence Aug 01 '16 at 18:40

3 Answers3

8

With System i Navigator on a PC, connect to your server and drill down into Databases and a schema. Click on either Tables or Views to list (possibly all) PFs or LFs in that library. Then, right-click the selections and select 'Generate SQL'. I suggest choosing to generate into 'Run SQL scripts'.

The result will be a SQL script with CREATE TABLE or CREATE VIEW statements for all selected objects. Most relevant LABEL statements will also be included for column attributes. You can either save the script into a .SQL file on the PC or a network share. I usually copy/paste the script rather than save it, but you'll want to experiment in order to understand how line endings (CR and LF) might be generated in your case.

Be sure to review (and possibly test) various options. Some might be better fits for your final objective.

As for multi-format LFs, I don't think I've seen them used on any system I've worked with since SQL became fully available. Since there is no good SQL equivalent, it's unlikely there is any automated method. Maybe someone knows an obscure possibility.

If your question involves a System/36 environment, edit your question to indicate so. Numerous additional comments might be needed.

user2338816
  • 2,163
  • 11
  • 11
  • Awesome! No System/36 by the way! Does System Navigator use the QSQGNDDL command to create the SQL statement? – LppEdd Jul 29 '16 at 07:29
  • 1
    @LppEdd Yes it does. – Adrian Bannister Jul 29 '16 at 07:33
  • 2
    @LppEdd, moving from DDS to SQL DDL is well documented. Take a look at Chapter 9 of [Modernizing IBM i Applications from the Database up to the User Interface and Everything in Between](https://www.redbooks.ibm.com/abstracts/sg248185.html?Open). Which basically the contents of this older Redbook [Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone](https://www.redbooks.ibm.com/abstracts/sg246393.html?Open) – Charles Jul 29 '16 at 13:00
3

Yes, there are cases where a file cannot be 'converted to SQL.'
Multiformat logicals are one such case. Program described physicals are another. There may be others where the API cannot generate DDL. This sounds as if you are replacing IBM i with something else. Be advised that DB2 for i is a somewhat different dialect to DB2 LUW, and is quite different to MS SQL Server or MySQL. Significant testing will be required to make sure the data comes across as desired.

Buck Calabro
  • 7,558
  • 22
  • 25
1

Yes one case is when the file has more than one member. Any pf that has max members greater than 1 will cause problems. You can create a list of all PF with multiple members with the dspfd command.

dspfd file(*all/*allusr) type(*mbr) output(*outfile) outfile(mylib/myfile)

Search the outfile for max members > 1.

Looky here for more info, we call this Cruikshanking where I work

danny117
  • 5,581
  • 1
  • 26
  • 35
  • Multi-member PFs are almost directly possible to convert to SQL tables, though it may take some minor programming changes along with adding a column or two that identifies member data. Also, for well financed sites, DB2 Multisystem might convert members to partitions. – user2338816 Jul 29 '16 at 18:35
  • @user2338816 absolutely you can add a column change the keys keep the GIGO system in place. However at V7.2 porting to DDL tables is a waste of man power. http://powerwire.eu/exclusive-ibm-i-7-2-delivers-17x-performance-gain-with-zero-change – danny117 Jul 29 '16 at 21:56
  • True enough, and as the SQE evolves, those still relying on Query/400 or OPNQRYF, etc., will see relief. Not sure how it relates to this question, though. Conversion to DDL brings advantages well beyond those two hopefully fading areas. – user2338816 Jul 30 '16 at 03:44