QTEMP conflict management on IBM i in C# with NTi

Introduction

The IBM i is the best information system, and there's no need to prove it. But the desire to continue to support all the techniques of the machines that preceded it means that it has to support resources which, although at one time very useful, have now become oddities.

The QTEMP library, inherited from the AS400 and S/38, is a case in point. I won't go into detail about how it works; just know that it is systematically created at the start of any job and deleted when the job stops. QTEMP belongs exclusively to the job that created it** and to it alone, and it can only be accessed by what is running inside it.

If things are clear in an AS400 use of the IBM i via the QINTER and QBATCH subsystems, things get complicated when the IBM i resources are used via the QUSRWRK subsystem by jobs of type QZDASOINIT and QZRCSRVS. I will explain this using a C# example.

Code Example

The sequence of C# code below provides exactly the same result: the duplication of a table in QTEMP, with the creation of TARTICL1 by a CPYF command and then TARTICL2 by a SQL CREATE TABLE command (this code uses NTi Data Provider, of course).

Try
{
   cx.ExecuteClCommand("CPYF FROMFILE(AIRELLES/TARTICL) TOFILE(QTEMP/TARTICL1) MBROPT(*REPLACE) CRTFILE(*YES)"); 
  
   cx.Execute("CREATE TABLE QTEMP.TARTICL2 AS (SELECT * FROM AIRELLES.TARTICL) WITH DATA");
}
catch (Exception ex)
{
   Console.WriteLine(ex.ToString());
}

💡 A breakpoint has been set on the last bracket.

At runtime, when the process reaches the breakpoint, we notice on the IBM i the presence of two jobs in QUSRWRK thanks to the WRKACTJOB SBS(QUSRWRK) command: QZDASOINIT and QZRCSRVS.

QZDASOINIT is dedicated to database operations via SQL.

QZRCSRVS supports all typical AS400 operations, such as CL commands, programs, etc.

Examining the structure of these two jobs

Below is the list of QZDASOINIT libraries:

And that of QZRCSRVS:

In both cases, we see the presence of a QTEMP library.

Let's examine the contents of these libraries using the 5 option of WRKACTJOB.

The QTEMP library of QZDASOINIT contains the TARTICL2 file:

QTEMP of QZRCSRVS contains the file TARTICL1 :

Consequences

This is where things get complicated.

• If I execute the SQL query:

select * from qtemp.tarticl1

⚠️ I get the message ‘TARTICL1 file not found in the QTEMP library’.

• If I run the CL command

CPYF FROMFILE(QTEMP/TARTICL2) TOFILE(QGPL/TARTICL2) MBROPT(*REPLACE) CRTFILE(*YES)**

⚠️ I get the message "TARTICL2 file not found in the QTEMP library ’.

• If I execute the SQL query :

select * from qtemp.tarticl2

✅ Everything ends normally.

• If I run the CL command:

CPYF FROMFILE(QTEMP/TARTICL1) TOFILE(QGPL/TARTICL2) MBROPT(*REPLACE) CRTFILE(*YES)

✅ There are no errors.

The problem stems from the fact that an SQL query is addressed to the QTEMP library associated with QZDASOINIT, whereas pure AS400 programs, commands and other resources use the QTEMP of the QZRCSRVS job.

As the use of the QTEMP library has gone out of fashion, it is very often older applications that use it (before SQL became widespread). Useful data is almost always found in the QTEMP of QZRCSRVS, and is therefore inaccessible via an SQL query.

As you can see, if you want to modernise by reusing existing RPG, COBOL, CLP programs in .NET, you'll come across cases where this won't work. You can get away with using a CLP program that will read the file from the correct QTEMP and return the data to you in a C# program. But, just for this problem, you'll have to teach your .NET developer how to write CL code.

A solution

The following C# function receives as parameters the definition of the connection to the IBM i previously established (cx), the QTEMP file to be used (QtempFile), and returns the data in a DataTable. Other means can be used to receive the data.

To simplify the comments, I will use the following notations to express which QTEMP library the execution of an instruction is addressed to:

• QZDASOINIT/QTEMP
• QZRCSRVS/QTEMP

This function will move the desired file from QZRCSRVS/QTEMP to QZDASOINIT/QTEMP so that it can be accessed via an SQL query.

public static DataTable QtempDt (NTiConnection cx, string QtempFile)
{
    var adapter = new NTiDataAdapter(); // Definition of a DataAdapter
    DataTable dt = new DataTable(); //  Definition of the result DataTable
    var cmd = cx.CreateCommand(); // Command definition in the NTi connection

    cmd.CommandText = "SELECT * FROM QTEMP." + QtempFile; // Composition of the SQL query with the file name
    adapter.SelectCommand = cmd; // Associating the command with the DataAdapter
   
    //  Data is retrieved using an SQL query, so the QtempFile will be searched for in the QTEMP library associated with the QZDASOINIT job.

    try
    {
        // If the file you are looking for was created on the IBM i by an SQL query, the instruction below is executed successfully (the QtempFile file is present in QZDASOINIT/QTEMP). 
        // The data is added to the DataTable 
        adapter.Fill(dt);
    }
    catch 
    {
     // Otherwise, the file must be copied to QZRCSRVS/QTEMP. 
     // To do this, we need an intermediate library. 
     // Here, to avoid duplication, I create a library whose name is made up of the concatenation of the characters ‘LB’ and the current time (HHMMSS). 
     // You can do things differently with random functions 

        string BibTemporaire = "LB" +         
        DateTime.Now.ToString().Substring(11).Replace(":", "");
        cx.ExecuteClCommand("CRTLIB LIB(" + BibTemporaire + ")");
        
        // Uses QZRCSRVS/QTEMP: the file is copied to the temporary database

        cx.ExecuteClCommand("CPYF FROMFILE(QTEMP/" + QtempFile + ") TOFILE(" +     
        BibTemporaire + "/" + QtempFile + ") MBROPT(*REPLACE) CRTFILE(*YES)");
        
        // The file is created with a copy of the data in QZDASOINIT/QTEMP This QTEMP can be accessed by an SQL query
        cx.Execute("CREATE TABLE QTEMP." + QtempFile + " AS (SELECT * FROM " + BibTemporaire + "." + QtempFile + ") WITH DATA");
        
        // The temporary library is deleted
        cx.ExecuteClCommand("DLTLIB LIB(" + BibTemporaire + ")");

        // The data is integrated into the DataTable
        adapter.Fill(dt);

        // The QtempFile is deleted in 
        cx.Execute("Drop table qtemp." + QtempFile);
    }

    // The DataTable is returned
    return dt;
}

This program of course uses the NTi Data Provider connector.

Its speed of execution means that this succession of instructions can be inserted without penalising response times.

Bonus

Both jobs are in the « TIMW » state, ready to receive a new request. The problem is that the QTEMP library from the previous request is still present... I'll let you work out for yourselves the inconsistencies that are likely to result.

Normally, after a certain amount of time without being used, these jobs disappear. But sometimes, these jobs remain indefinitely in the « TIMW » state without being reused; there is then a pile of lines in QUSRWRK which you don't know what to do with: stop them, keep them... Consulting sites on the internet doesn't clarify anything.

This risk does not exist with NTi Data Provider, because the QZDASOINIT and QZRCSRVS jobs started by NTi never go to the « TIMW » or « PSRW » state when the C# session is over. They are systematically destroyed, even when the program in progress is abruptly stopped (and therefore restarted and reinitialised each time). There is no risk of reusing them at a later date.

And this restart has no impact on the performance of .NET with NTi.

Laurent Rouillot