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.

Wednesday, April 29, 2015

Coping with data decimal error when read file

data decimal error pssr fixnbr and monitor group

I am sure we have experienced running a program when it errors with "Decimal-data error occurred", RNQ0907. After analyzing the program I would find that the error is caused by bad data in a field in the input file. Yes, I will correct the bad data, but I might want to have the program continue using a default value and create a dump that can be used for analysis later. Examples of creating dumps can be found in the post: Producing a dump from programs.

How can I flag the record as having an error, give the numeric field a default value, and continue processing?

Tuesday, April 28, 2015

More on IBM i 7.2 TR2 and 7.1 TR10

More information for the new Technology Refreshes, IBM i 7.2 TR2 and 7.1 TR10, is slowly coming out.

The release date for these is May 29 2015 with the following PTF numbers:

  • IBM i 7.1
    • SI55340 TGTRLS(*CURRENT)
  • IBM i 7.2
    • SI55442 TGTRLS(*CURRENT)
    • SI55531 TGTRLS(V7R1M0)

IBM's announcement letter can be found here.

The enhancements RPG can be found in the "Whats new since 7.2", mixed with the 7.2 TR1 enhancements, here.

And the MC Press has an article about the DB2 for i (SQL) changes here.

Once I can get access to a server with one of these Technology Refreshes I will start writing about them here.

Monday, April 27, 2015

IBM i 7.2 TR2 and 7.1 TR10 DB2 for i enhancements

The details for what is included in IBM i 7.2 Technology Refresh 2, TR2, and IBM i 7.1 TR10 is now out!

IBM has a page for the DB2 for i (SQL) enhancements in these Technology Refreshes: here.

It would appear that as both the Technology Refreshes contain the same the details are given on the TR2 page.

Things that caught my eye are:

  • CREATE OR REPLACE TABLE
  • JSON DB2 store
  • RUNSQL control of output listing (which was later removed from the page)

Now I need to find what enhancements, if any, have been made to RPG and CL.

Sunday, April 26, 2015

IBM i 7.1 TR10 and 7.2 TR2 announced at Common

Today at the Common User group meeting at Disneyland new Technology Refreshes were announced:

  • IBM i 7.1 TR10
  • IBM i 7.2 TR2

No details yet, but there are pages on IBM's website for both Refreshes:

As soon as I learn more about these I will publish it here on this blog.




Update - Monday April 27 6:30 AM

Steve Will's, IBM i Chief Architect, presentation on what is in IBM i 7.2 TR2 is ongoing at the Common meeting. He has given us a taste of what is to come in a blog post here.

Wednesday, April 22, 2015

Producing a dump from programs

dump programs dump dmpclpgm ovrprtf

There are times when an error is encountered in a program, particularly one that is running in batch, which I do not want to "error out" with a message. I want to cope with the error within the program, flag that there was an error so that I can resolve the issue later, and continue processing. If I can generate a program dump when the error occurs I have all the information I need to be able to fix it. CL's DMPCLPGM command and RPG's DUMP operation code do that without the need to break for an error message, producing the same output as if I had answered an error message with an option "D".

I am surprised at the number of IBM i developers who are unaware that it is possible to produce a dump without an error message. I would prefer to cope with errors and produce a dump within a program rather than receiving a 2 AM phone call from the night operator telling me that there is an error that is holding everything else up, and what am I going to do about it?

Tuesday, April 21, 2015

Video: Why Modernization on IBM i matters

This is a video of the presentation Tim Rowe, Business Architect for application development on IBM i, made to Common Europe:

Monday, April 20, 2015

IBM offers extended support for IBM 6.1 and 6.1.1

Previously IBM had announced that support for the IBM i 6.1 and 6.1.1 versions of their operating system would end on September 30, 2015. On April 14 they announced a "service extension for IBM I V6R1":

IBM Software Support Services - service extension for IBM i V6R1 provides support for usage questions, problem determination assistance, and support for new fixes. With this service, IBM product specialists can help you simplify management of your IBM i 6.1 operating system as well as resolve issues more quickly and efficiently when they do happen, thereby minimizing risk of business disruption.