Data Management Global

 View Only

Calling unqualified external stored procedures from Db2 for i user library using IBM Data Server Provider for .NET Core or .NET5

By Tapan Ghosh posted Thu May 13, 2021 02:21 AM

  
While migrating applications from Db2 for IBM i .NET Provider to IBM Data Server Provider for .NET Core or .NET5, we need to take care of different aspects of both providers. The unqualified object names refer to an object without specifying the schema, library etc. in which the object resides. Here we are going to elaborate about few IBM Data Server Provider for .NET Core and .NET 5 provider-specific properties which help in accessing unqualified object names such as tables, external stored procedures (SP)  from user portion of libraries of Db2 for i database. These are mentioned below :

Note : Throughout the document, unless otherwise stated, .NET Core is used to represent both .NET Core 3.1 and .NET 5.

LibraryList
For IBM Data Server Provider for .NET Core, the LibraryList keyword of ConnectionString property enables us to set the user portion of the library list for Db2 for i database. It allows user to specify which libraries are to be loaded by Db2 for i database for the current connection. With IBM Data server provider for .NET Core, LibraryList keyword must contain the list of library names separated by spaces where as in case of Db2 for IBM i .NET Provider, LibraryList keyword accepts a comma delimited list of libraries. LibraryList keyword can be set through DB2Connection class ConnectionString property and DB2ConnectionStringBuilder class property.

Reference IBM info-center link:
LibraryList keyword of DB2Connection class ConnectionString Property 
LibraryList Property of DB2ConnectionStringBuilder class

SystemNaming

In IBM Data Server Provider for .NET Core, SystemNaming property of DB2Connection class is used to either set the naming convention to SQL or System. SystemNaming property accepts boolean value where TRUE indicates system naming convention and FALSE indicates SQL naming convention. The default naming convention is SQL. This property is supported through only DB2Connection class property. Where as for Db2 for IBM i .NET Provider, Naming attribute is supported through ConnectionString property which is used to set the IBM i naming convention used for SQL requests. Naming attribute accepts a value of System and SQL. System value indicates that system naming convention should be used, and SQL value indicates that the SQL naming convention should be used (SQL is the default).

CurrentFunctionPath
In IBM Data Server Provider for .NET Core, CurrentFunctionPath keyword of ConnectionString  property is used to list one or more schema names, where the schema names are separated by commas. If CurrentFunctionPath keyword is not set explicitly through ConnectionString property, then the default value of the CURRENT PATH special register is "QSYS","QSYS2","SYSPROC","SYSIBMADM","NEWTON" (where NEWTON is the value of the USER special register) on Db2 for i. The CurrentFunctionPath property is used to resolve unqualified function and stored procedure, external stored procedure references that resides in a schema name and library other than the current user's schema. The CurrentFunctionPath keyword can be set through ConnectionString property, DB2ConnectionStringBuilder class property and db2dsdriver.cfg configuration keyword option.

Reference Info-center link:
CurrentFunctionPath keyword of the DB2Connection class ConnectionString property
CurrentFunctionPath property of DB2ConnectionStringBuilder class
CurrentFunctionPath IBM data server driver configuration keyword

In the following examples, we are going to cover how to access unqualified database objects  such as tables, external stored procedures (SP)  from user portion of library in Db2 for i database in different scenarios when we are using IBM Data Server Provider for .NET Core. Let us consider them one by one.

Scenario - 1
Let us assume, there are many external stored procedures defined within multiple different user libraries of Db2 for i database. Then, to access those external stored procedures without fully qualifying, we need to set CurrentFunctionPath keyword with list of libraries separated by commas.

For example, assuming
- One external stored procedure named extSP1 is defined in library LIBTST1.
- Another external SP named extSP2 is defined in library LIBTST2.
- And we want to call these external stored procedures without fully qualifying it in ADO.NET application. Then we need to assign keyword CurrentFunctionPath=LIBTST1,LIBTST2 with library names in connection string as shown in below example code.

using System;
using System.Data;
using IBM.Data.DB2.Core;

namespace CFPath
{
class Program
{
static void Main(string[] args)
{
string conString = "Server=ServerName:PortNumber;Database=dbname;" +
"User ID=myUsername;pwd=myPassword;" +
"CurrentFunctionPath=LIBTST1,LIBTST2";

DB2Connection connection = new DB2Connection(conString);

connection.Open();
Console.WriteLine("Connection Opened Successfully");

DB2Command command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;

//extSP1 external stored procedure is defined in LIBTST1 library
command.CommandText = "extSP1";

DB2DataReader reader = command.ExecuteReader();
while (reader.Read())
Console.WriteLine(reader.GetValue(0));

reader.Close();

//extSP2 external stored procedure is defined in LIBTST2 library
command.CommandText = "extSP2";
reader = command.ExecuteReader();
while (reader.Read())
Console.WriteLine(reader.GetValue(0));

reader.Close();
connection.Close();
}
}
}

Scenario - 2
However, if we want to access both external stored procedures defined within user libraries and stored procedures defined within different schemas on Db2 for i database through ADO.NET application. Then we will only need to set CurrentFunctionPath property with list of libraries, schemas separated by commas.

For example, assuming
- One external stored procedure extSP1 is defined within user library name LIBTST1
- Another external SP named extSP2 is defined within user library name LIBTST2
- Stored procedure mySP is defined under database schema ZURBIE.
And we want to call both external stored procedures extSP1, extSP2 and stored procedure mySP without fully qualifying it in ADO.NET application. Then we need to assign keyword CurrentFunctionPath=LIBTST1,LIBTST2,ZURBIE with library names and schema names in our connection string as shown in below example code.

using System;
using System.Data;
using IBM.Data.DB2.Core;

namespace CFPathSchema
{
class Program
{
static void Main(string[] args)
{
string conString = "Server=ServerName:PortNumber;Database=dbname;" +
"User ID=myUsername;pwd=myPassword;" +
"CurrentFunctionPath=LIBTST1,LIBTST2,ZURBIE";

DB2Connection connection = new DB2Connection(conString);
connection.Open();
Console.WriteLine("Connection Opened Successfully");

DB2Command command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;

//extSP1 external stored procedure is defined in LIBTST1 library
command.CommandText = "extSP1";

DB2DataReader reader = command.ExecuteReader();
while (reader.Read())
Console.WriteLine(reader.GetValue(0));

reader.Close();

//extSP2 external stored procedure is defined in LIBTST2 library
command.CommandText = "extSP2";
reader = command.ExecuteReader();
while (reader.Read())
Console.WriteLine(reader.GetValue(0));

reader.Close();

//mySP stored procedure is defined in ZURBIE database schema
command.CommandText = "mySP";
reader = command.ExecuteReader();
while (reader.Read())
Console.WriteLine(reader.GetValue(0));

reader.Close();
connection.Close();
}
}
}

Scenario - 3
Suppose we have created tables within user portion of libraries on DB2 for i database. Then, to access only those tables created within specific libraries on DB2 for i without fully qualifying, we need to specify both LibraryList keyword and SystemNaming property. For that we need to set LibraryList keyword with list of libraries where tables were created and SystemNaming property needs to be set to True as shown in below example code.

For example, assuming
-Table named STUDENT is defined in LIBTST3 user library.
-Table named ORDER is defined in LIBTST4 user library.
Here are the table definition of both STUDENT and ORDER tables.

CREATE TABLE "LIBTST3"."STUDENT" (
"STUDENTID" INTEGER ,
"LASTNAME" VARCHAR ( 20 ) ,
"FIRSTNAME" VARCHAR ( 20 ) )

CREATE TABLE "LIBTST4"."ORDER" (
"ORDERID" INTEGER ,
"CUSTOMERNAME" VARCHAR ( 20 ) ,
"STATUS" VARCHAR ( 20 ) )

So, for accessing STUDENT and ORDER table without fully qualifying it, requires LibraryList to be set with user library LIBTST3 and  LIBTST4. Also SystemNaming needs to be set to True as shown in below example code.

using System;
using System.Data;
using IBM.Data.DB2.Core;

namespace LibList
{
class Program
{
static void Main(string[] args)
{
string conString = "Server=ServerName:PortNumber;Database=dbname;" +
"User ID=myUsername;pwd=myPassword;" +
"LibraryList=LIBTST3 LIBTST4";

DB2Connection connection = new DB2Connection(conString);

connection.SystemNaming = true;
connection.Open();
Console.WriteLine("Connection Opened Successfully");

DB2Command command = connection.CreateCommand();
//STUDENT table is defined in LIBTST3 library
command.CommandText = "select * from STUDENT";

DB2DataReader reader = command.ExecuteReader();

while (reader.Read())
{
Console.WriteLine("Studentid: " + reader.GetValue(0));
Console.WriteLine("LastName: " + reader.GetValue(1));
Console.WriteLine("FirstName: " + reader.GetValue(2));
}
reader.Close();

//ORDER table is defined in LIBTST4 library
command.CommandText = "select * from ORDER";
reader = command.ExecuteReader();

while (reader.Read())
{
Console.WriteLine("Orderid: " + reader.GetValue(0));
Console.WriteLine("CustomerName: " + reader.GetValue(1));
Console.WriteLine("Status: " + reader.GetValue(2));
}
reader.Close();
connection.Close();
}
}
}

Scenario- 4
Suppose we have created tables within both libraries and schemas on Db2 for i  database. Then, to access tables created within libraries and schemas on DB2 for i database without fully qualifying, we need to specify both LibraryList keyword and SystemNaming property. For that we need to set LibraryList keyword with list of libraries and schemas where we defined our tables and SystemNaming property set to True. 

For example, assuming
-Table STUDENT is defined in LIBTST3 library.
-Table EMPLOYEE is defined under schema ZURBIE.
Here are the table definition of both tables STUDENT and EMPLOYEE.

CREATE TABLE "LIBTST3"."STUDENT" (
"STUDENTID" INTEGER ,
"LASTNAME" VARCHAR ( 20) ,
"FIRSTNAME" VARCHAR ( 20 ) )

CREATE TABLE "ZURBIE"."EMPLOYEE" (
"EMPNO" INTEGER ,
"SAL" DECIMAL ( 8,3 ) ,
"EMPNAME" VARCHAR ( 20 ) )

So, for accessing STUDENT and EMPLOYEE table without fully qualifying it, requires LibraryList to be set with LIBTST3, ZURBIE and SystemNaming to be set to True as shown in below example code.

using System;
using System.Data;
using IBM.Data.DB2.Core;

namespace LibListSchema
{
class Program
{
static void Main(string[] args)
{
string conString = "Server=ServerName:PortNumber;Database=dbname;" +
"User ID=myUsername;pwd=myPassword;" +
"LibraryList=ZURBIE LIBTST3";

DB2Connection connection = new DB2Connection(conString);
connection.SystemNaming = true;
connection.Open();
Console.WriteLine("Connection Opened Successfully");

DB2Command command = connection.CreateCommand();
//STUDENT table is defined in LIBTST3 library
command.CommandText = "select * from STUDENT";
DB2DataReader reader = command.ExecuteReader();

while (reader.Read())
{
Console.WriteLine("Studentid: " + reader.GetValue(0));
Console.WriteLine("LastName: " + reader.GetValue(1));
Console.WriteLine("FirstName: " + reader.GetValue(2));
}
reader.Close();

//EMPLOYEE table is defined in ZURBIE database schema
command.CommandText = "select * from employee";
reader = command.ExecuteReader();

while (reader.Read())
{
Console.WriteLine("Employee No: " + reader.GetValue(0));
Console.WriteLine("Salary: " + reader.GetValue(1));
Console.WriteLine("Employee Name: " + reader.GetValue(2));
}
reader.Close();
connection.Close();
}
}
}

Scenario- 5 
As explained above, let tables STUDENT, ORDER are defined in user library LIBTST3, LIBTST4 respectively. And EMPLOYEE table is defined in schema ZURBIE. Similarly let external stored procedure named extSP1, extSP2 are defined in library LIBTST1, LIBTST2 respectively. And stored procedure mySP is defined in schema ZURBIE.

Then, to access tables (STUDENT, ORDER, EMPLOYEE), external stored procedures (extSP1,extSP2) and stored procedures (mySP) together without fully qualifying in ADO.NET application; we need to set LibraryList, CurrentFunctionPath keyword with list of libraries and schemas. Also, SystemNaming property needs to be set to True as shown in below example code.

using System;
using System.Data;
using IBM.Data.DB2.Core;

namespace LibListSchemaCFPathSchema
{
class Program
{
static void Main(string[] args)
{
string conString = "Server=ServerName:PortNumber;Database=dbname;" +
"User ID=myUsername;pwd=myPassword;" +
"LibraryList=ZURBIE LIBTST3 LIBTST4;" +
"CurrentFunctionPath=LIBTST1,LIBTST2,ZURBIE";

DB2Connection connection = new DB2Connection(conString);

connection.SystemNaming = true;
connection.Open();
Console.WriteLine("Connection Opened Successfully");

DB2Command command = connection.CreateCommand();
DB2DataReader reader = new DB2DataReader();
command.CommandType = CommandType.StoredProcedure;

//extSP1 external stored procedure is defined in LIBTST1 library
command.CommandText = "extSP1";
reader = command.ExecuteReader();
while (reader.Read())
Console.WriteLine(reader.GetValue(0));

reader.Close();

//extSP2 external stored procedure is defined in LIBTST2 library
command.CommandText = "extSP2";
reader = command.ExecuteReader();
while (reader.Read())
Console.WriteLine(reader.GetValue(0));

reader.Close();

//mySP stored procedure is defined in ZURBIE database schema
command.CommandText = "mySP";
reader = command.ExecuteReader();
while (reader.Read())
Console.WriteLine(reader.GetValue(0));
reader.Close();

//STUDENT table is defined in LIBTST3 library
command.CommandText = "select * from STUDENT";
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();

while (reader.Read())
{
Console.WriteLine("Studentid: " + reader.GetValue(0));
Console.WriteLine("LastName: " + reader.GetValue(1));
Console.WriteLine("FirstName: " + reader.GetValue(2));
}
reader.Close();

//ORDER table is defined in LIBTST4 library
command.CommandText = "select * from ORDER";
reader = command.ExecuteReader();

while (reader.Read())
{
Console.WriteLine("Orderid: " + reader.GetValue(0));
Console.WriteLine("CustomerName: " + reader.GetValue(1));
Console.WriteLine("Status: " + reader.GetValue(2));
}
reader.Close();

//EMPLOYEE table is defined in ZURBIE database schema
command.CommandText = "select * from employee";
reader = command.ExecuteReader();

while (reader.Read())
{
Console.WriteLine("Employee No: " + reader.GetValue(0));
Console.WriteLine("Salary: " + reader.GetValue(1));
Console.WriteLine("Employee Name: " + reader.GetValue(2));
}
reader.Close();
connection.Close();
}
}
}


#DataManagementGlobal
#DataServerDrivers
0 comments
48 views

Permalink