using System;
using com.ibm.db2.jcc;
using java.sql;
using Thread = java.lang.Thread;
using Class = java.lang.Class;
using String = System.String;
using Connection = java.sql.Connection;
using Statement = java.sql.Statement;
using DriverManager = java.sql.DriverManager;
using ResultSet = java.sql.ResultSet;

namespace jdbc
{
	class Program
	{
		static void Main(string[] args)
		{
			Class.forName(
				typeof(com.ibm.db2.jcc.DB2Driver).AssemblyQualifiedName, true, 
				Thread.currentThread().getContextClassLoader());

			String url = "jdbc:db2://192.168.72.135:50000/DB1:user=root;password=password;";
			using (Connection conn = DriverManager.getConnection(url))
			{
				String sql = "SELECT * FROM TABLE(VALUES( CAST( :p AS VARCHAR(100)) , 'It is work')) AS T(ID, LOG)";
				using (var stmt = conn.prepareCall(sql))
				{
					stmt.setString("p","1234");
					using (ResultSet rs = stmt.executeQuery())
					{
						while (rs.next())
						{
							//Retrieve by column name
							String id = rs.getString("ID");
							String log = rs.getString("LOG");
							global::System.Console.WriteLine("LOG: " + id  + " : " + log);
						}
					}
				}
			}
		}
	}
}

static Db2Connection()
{
	Class.forName(
		typeof(com.ibm.db2.jcc.DB2Driver).AssemblyQualifiedName, true, 
		Thread.currentThread().getContextClassLoader());
}

Class.forName("com.ibm.db2.jcc.DB2Driver");

connector = DriverManager.getConnection(_connectionString);

jdbc:db2://192.168.72.135:50000/DB1:currentSchema=DB01;user=root;password=password;fullyMaterializeLobData=true;DB2NETNamedParam=1;

new Regex(@"\(:(?<n>\w+)\d+,:\1\d+\)");

SqlMapper.AddTypeHandler(DapperULongHandler.Default);

Магический метод номер раз

internal static CallableStatement PrepareExecute(Db2Connection connection, string query, Db2ParameterCollection parameters)

Магический метод номер два

internal static string PrepareCommandText(string query, Db2ParameterCollection parameters, ref int parameter)

Последний магический метод

private static void PrepareParameters(CallableStatement stmt, Db2ParameterCollection parameters)

public override DbType DbType

public object Clone()

public override int IndexOf(string parameterName)

new Regex(@"(?<q>[^;]+);?");

new Regex(@"(?<n>:\w+)");

var b = _statement.getMoreResults();
if (b)
{
	_rs = _statement.getResultSet();
}
else
{
	_statement = PrepareStatementAndResultSet();
	if (_statement != null)
	{
		_rs = _statement.executeQuery();
		b = true;
	}
}

SELECT 
    r.ID
    , r.INCOME
    , r.GU_CODE as GuCode
	, r.NO as No
    , r.DIVISION
	, d.NAME as DivisionName
	, u.LNAME || ' ' || u.FNAME || nvl(' ' || u.MNAME, '') as CreaterName
	, r.SIGNED
	, r.SIGNED_SYSUSER_ID as SignedID
	, su.LNAME || ' ' || su.FNAME || nvl(' ' || su.MNAME, '') as SignedName
    --, (select count(*) from PACK_ENTRY p where p.REGISTRY_ID = r.ID) as PackCount -- Use C# code
    --, (select count(*) 
    --    from PACK_ENTRY p 
    --    join FILE_ENTRY f on f.PACK_ID=p.ID
    --    where p.REGISTRY_ID = r.ID) as FileCount -- Use C# code
    -- TODO 2016-04-13 emiya: Добавить доп.поля для включения информции в таблицу REGISTRY_ENTRY
    , (select p.CLOSE_DATE from PACK_ENTRY p join REGISTRY_TO_PACK p2p on p2p.REGISTRY_ID = r.ID and p2p.PACK_ID = p.ID order by p.INCOME fetch first 1 rows only) as CloseDate
    , (select p.CATEGORY_ID from PACK_ENTRY p join REGISTRY_TO_PACK p2p on p2p.REGISTRY_ID = r.ID and p2p.PACK_ID = p.ID order by p.INCOME fetch first 1 rows only) as CategoryCode
    , (select l.SHORTNAME from PACK_ENTRY p 
        join CATEGORY_LIST l on l.ID = p.CATEGORY_ID
        join REGISTRY_TO_PACK p2p on p2p.REGISTRY_ID = r.ID and p2p.PACK_ID = p.ID
        order by p.INCOME fetch first 1 rows only) as CategoryName

   , p2p.REGISTRY_ID
   , p.ID
   , p.INCOME
   , p.CLOSE_DATE as CloseDate
   , p.CATEGORY_ID as CategoryCode
   , l.SHORTNAME as CategoryName
   , p.GU_CODE as GuCode
   , p.NO
   , p.DIVISION
   , p.SYSUSER_ID as SysUserID
   , (select count(*) from FILE_ENTRY where PACK_ID = p.ID) as Count

   , r.SIGNED
   , p.PLACE
   , p2p.STATUS
   , p2p.NOTE
from REGISTRY_ENTRY r
join DIVISION d on d.CODE=r.DIVISION
join SYSUSER u on u.ID=r.SYSUSER_ID
    LEFT JOIN SYSUSER su ON su.ID=r.SIGNED_SYSUSER_ID

	join REGISTRY_TO_PACK p2p on p2p.REGISTRY_ID = r.ID
		join PACK_ENTRY p on p.ID = p2p.PACK_ID
			join CATEGORY_LIST l on l.ID = p.CATEGORY_ID
WHERE r.ID=? AND @DIVISION
order by r.ID,p.GU_CODE,p.NO