Wednesday, February 21, 2018

Having the source for suggested indexes created for you

using sql to created advised indexes for you

Last year I wrote about using the Db2 for i index advisor to suggest indexes that would improve the performance of your SQL statements. I showed how you could copy data from its results to use to create for SQL statements to create the suggested Indexes. Recently I found a Db2 for i Procedure that makes this so much easier as it will create source members for the suggested Indexes.

I can use the Index Advisor View to see what Indexes are suggested for the FILE1 in the library MYLIB.

01  SELECT SYS_DNAME AS LIBRARY,
02           SYS_TNAME AS TABLE,
03           FIRSTADV AS FIRST_ADVISED,
04           TIMESADV AS NBR_TIMES,
05           KEYSADV AS ADVISED_KEYS
06     FROM QSYS2.SYSIXADV
07    WHERE SYS_DNAME = 'MYLIB'
08      AND SYS_TNAME = 'FILE1'

Wednesday, February 14, 2018

Making IP connections easier between IBM i partitions

ddm server authority entries

I have mentioned in previous posts regarding using DDM files and configuring remote database connections that there is a difference in using SNA and IP for the connection protocol.

When you use SNA to connect everything performed on the remote IBM i is performed as the user profile QUSER. It is impossible to discriminate between valid requests for the files/tables and others trying to get at data they should not be authorized to.

CRTDDMF FILE(QTEMP/DDMFILE1)
          RMTFILE(OTHERLIB/RMTFILE)
          RMTLOCNAME(SYSTEM2)

Tuesday, February 13, 2018

IBM i 7.3 TR4 and 7.2 TR8 announced

ibm i 7.3 tr4 and ibm i 7.2 tr 8 announced

I find it interesting that the announcements of new Technical Refreshes are always made on a Tuesday. After finding empty pages for the new TRs last week it was obvious that the announcement would happen soon. What does the latest announcement contain:

The biggest announcement are the new Power9 chips are coming.

Wednesday, February 7, 2018

Configuring database connection between two IBM i

configuring database connection to another ibm i

In my day job I work in an environment where there are multiple PowerSystems servers, and many of them have multiple IBM i partitions. I was tasked to gather information from most of the partitions into one location. I decided to use SQL to fetch the information from the various sources.

I used the following two methods to connect to the other partitions using the partition name:

CONNECT TO SYSTEM2
  or  
SELECT * FROM SYSTEM2.OTHERLIB.RMTFILE

When debugging my program I found that when I tried to connect to some of the partitions I received a SQLCOD value of -950 and the following message:

Tuesday, February 6, 2018

New Technology Refreshes are coming

are ibm i 7.3 tr4 and 7.2 tr8 coming soon?

In the past the twice yearly Technology Refreshes updates for the current releases of IBM i were announced in the middle of February and the beginning of October, to coincide with Common USA events.

While Common does not appear to have a Spring conference this year (at the time I am writing this post their website is unavailable), the announcement of IBM i 7.3 TR4 and 7.2 TR8 could still be on track for this month as I have found empty pages for them in IBM developerWork's website.

Wednesday, January 31, 2018

Getting the library list from a Job Description

using api to get library list from jobd

The germ for this post came from this question:

How can get the Library List from a Job Description?

I can use the DSPJOBD command to see the Job Description's library list, alas I can only display or print it. This does not make it easy for me to use in a program.

DSPJOBD JOBD(MYLIB/MYJOBD)


Display Job Description
  Job description:  MYJOBD         Library:  MYLIB

  Initial library list:
    Sequence
     number   Library
        10    QTEMP
        20    MYLIB
        30    ANOTHERLIB
        40    QGPL

Wednesday, January 24, 2018

SQL Views to list SQL programs and the statements within them

use sql views to find out about programs containing sql

I found these two Views when going through the list of Db2 for i Views on Scott Forstie's Db2 for i poster (if you have not printed this out and stuck it to your wall do so now!). Both contain information about programs containing SQL statements:

  1. SYSPROGRAMSTAT:  One row for each program that contains a SQL statement
  2. SYSPROGRAMSTMTSTAT:  One for each SQL statement in a program

There are a couple for gotchas I found working with these Views, which I was disappointed by, I will mention these shortcomings when I discuss the View in detail below.

Wednesday, January 17, 2018

Using RPG data structures with SQL insert and update

rpg data structures with sql inserts and updates

As I can fetch rows using SQL from a table, or file, into a data structure. I was asked:

Can I insert or update rows using a RPG data structure?

The answer is a definite "Yes".

In these examples I am going to be inserting and updating the following table:

01  CREATE TABLE QTEMP.TABLE1 (
02    COLUMN1 CHAR(10) NOT NULL,
03    COLUMN2 DECIMAL(11,2),
04    COLUMN3 DATE,
05    COLUMN4 TIMESTAMP
06  ) ;

Wednesday, January 10, 2018

Displaying more than one subfile at a time

more than 1 subfile on a screen

I decided to write the post after being asked if I had an example of having two subfiles on the same screen. I did a quick search using Google and was unable to find what I considered a good example I felt comfortable sharing.

In this example I am going to show two subfiles horizontally (i.e. one on top of the other). I could have shown them vertically (next to one another), or even had more than two. The principals I show here can easily be adapted to fit either of those other scenarios. The most important thing to remember is that each set of subfile and subfile control record format cannot overlap another.

To reduce the size of the display file source code, shown here, I have removed all unnecessary color and display attributes codes that I use in the screens I build. I am going to show my display source in multiple parts to make it easier to understand what each record format is for. Let me start with the file level keywords.

Wednesday, January 3, 2018

Determining length of a string using SQL

calculating length of data within a column

This is just a quickie in response to a question I was asked: How can I determine the length of string within a column using SQL?

This is very simple as there is Db2 for i scalar function to do this LENGTH. But before I do that let me explain why using this scalar function is simpler than doing the same using RPG.

I have a simple DDL table of just one column. There is no "NOT NULL", therefore, this column can be null.

CREATE OR REPLACE TABLE QTEMP.TESTFILE (
  FIELD1 VARCHAR(80)
) ;

And it contains the following data: