Friday, May 29, 2015

New TRs available today

technology refresh tr2 tr10

The latest Technology Refreshes for IBM i, TR2 for 7.2 and TR10 for 7.1, are available today to download. You can learn more about them from IBM's developerWorks web site here.

You can learn more about what is in these Technology Refreshes from these posts:

Wednesday, May 27, 2015

Getting information about Views and Indexes

sql views sysviews sysviewdep sysindexes sysindexstat

We have been given a number of SQL views that allow us to harvest information from our IBM i's, and many are unaware of their existance.

With the move from DDS files to SQL tables, indexes, and views I needed to update my toolset to be able to list information about the last two in a quick to run and easy to read way. Fortunately I can use some of these views to do so:

Wednesday, May 20, 2015

Creating derived columns in SQL View

create view case column

A SQL View is a virtual table. Its columns can come from one or more SQL tables or DDS files. The data in the View is not stored in its own database object. When the View is used the Select statement is executed to produce the data. You can also create, what I call, "derived" columns. The "derived" columns do not exist in the original tables/files, they are calculated when the Select statement is run.

In the examples I am going to show how I can code "derived" fields in a View to:

Wednesday, May 13, 2015

Converting DDS files to SQL DDL using SQL

file dds to sql ddl conversion using sql generate_sql

In my last post I wrote about how to convert DDS files to SQL DDL using the QSQGNDDL API, you can read it here, and I mentioned that in IBM i 7.2 and 7.1 TR9 there was an alternative, the GENERATE_SQL SQL procedure.

Having used both I prefer GENERATE_SQL as it is simpler to use, just one statement in STRSQL, and it has the ability to use wild cards for mass conversions. Many of its parameters are in English, which makes it easier for others to understand my code. The has six mandatory parameters:

Wednesday, May 6, 2015

API to convert files to SQL

convert dds to sql table view index using qsqlgnddl

As part of the IBM i 7.1 release IBM notified us all of their intentions not to perform any future enhance to DDS for data (physical and logical) files, and are encouraging us to migrate those files to their equivalents in SQL (tables, views, and indexes). For many of us this is an overwhelming proposition as the applications we support contain many thousands of files. To convert these by hand would take a very long time. Fortunately IBM has an API, 'Generate Data Definition Language' (QSQLGNDDL), to assist.