Wednesday, November 10, 2010

Update DB2: SQL query exceeds specified time limit or storage limit




Hey Reader,
I wrote a .Net console application to read data from SQL Server and update respective table in DB2. So the update query is built dynamically in the code. The connection used was ODBC. When command.ExecuteNonQuery method is processed there occured an exception with the following message.

Message:
Message = "ERROR [HY000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - SQL query exceeds specified time limit or storage limit."

When googled came acorss this beautiful
post. And the solution is in ODBC properties under Performance tab , under Advance, we just need to uncheck the option - "Allow query timeout".
the trick worked for me :)
Hope it helpz
Fauzi


Monday, November 8, 2010

Sample to Connect DB2 through DOTNET

Dear Reader,
Here is a sample to connect DB2 through .Net Applicaiton, Create for a friend, thought this must be useful for people looking for it.

Code:
------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Data;

namespace DB2_ODBC_Sample
{
class Program
{
static void Main(string[] args)
{
try
{
OdbcCommand cmd = null;
OdbcConnection con = null;
OdbcDataReader myReader = null;

con = new OdbcConnection("DSN=[YOURDSN];UID=[YOUR USERNAME];PWD=[YOUR PASSWORD];" + "Driver={IBM DB2 ODBC DRIVER};");
cmd = new OdbcCommand();
cmd.Connection = con;

cmd.CommandText = "SELECT * FROM SOMETABLE fetch first 5 rows only";

cmd.CommandTimeout = 0;
con.Open();
myReader = cmd.ExecuteReader(System.Data.CommandBehavior.KeyInfo);
aawservice objaawservice = new aawservice();
DataSet objDataSet = objaawservice.ConvertDataReaderToDataSet(myReader);
con.Close();
for (int i = 0; i < objDataSet.Tables[0].Rows.Count; i++)
{
Console.WriteLine("\n");
Console.WriteLine(objDataSet.Tables[0].Rows[i][2].ToString());

}
Console.Write("\nPress [Enter] to Exit ... ");
Console.ReadLine();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}

}
class aawservice
{
public DataSet ConvertDataReaderToDataSet(System.Data.Odbc.OdbcDataReader reader)
{
DataSet dataSet = new DataSet();
do
{
// Create data table in runtime
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable();

if (schemaTable != null)
{
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];

// Create a column name as provided in Schema
string columnName = (string)dataRow["ColumnName"];

// Define Column Type here
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);

//Adding Column to table
dataTable.Columns.Add(column);
}

dataSet.Tables.Add(dataTable);

// Fill the data table from reader data

while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
dataRow[i] = reader.GetValue(i);
dataTable.Rows.Add(dataRow);
}
}

else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}
while (reader.NextResult());
return dataSet;
}
}

Thursday, November 4, 2010

In a application there is an option to upload file and the file name is dynamically set with user's Name and Phone number. When user fills the phone number as +965-97324246. The file name is saved a Name_+965-97324246.pdf. Since "+" is there the hyper link ofthe file gave error when we try to view in browser. Thus we can use Regex to ignore special charaters. please find the sample below:

Sample:

using System;

using System.Text.RegularExpressions;

namespace Regex_Replace

{

// Remove forbidden chars for filename

class Class1

{

static void Main(string[] args)

{

string sourceStr = @"1\2/3:4*5?6""7<8+>90";

string rgPattern = @"[\\\/:\*\?""<>+]";

Regex oRegex = new Regex(rgPattern);

Console.WriteLine(oRegex.Replace(sourceStr, ""));

Console.Write("\nPress [Enter] to Exit ... ");

Console.ReadLine();

}

}

}

Hope it Helps...

Happy Diwali :)