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

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;
}
}

C#.Net : Method to get Last date of Month & Julian Date

Hi...

We had a requirment to run an application for the last day of the month. The following method was helpful to get the date.
Sample Code:

private DateTime GetLastDayofMonth(DateTime dtDate)

{

DateTime dtToDate = dtDate;

dtToDate = dtToDate.AddMonths(1);

dtToDate = dtToDate.AddDays(-(dtToDate.Day));

return dtToDate;

}

// Later this code was helpful to convert it to julian date:

public string GetJulian_LastDayofPreviousMonth()

{

int intJulianDate = 1000 * (DateTime.Now.AddMonths(-1).Year - 1900) + GetLastDayOfMonth(DateTime.Now.AddMonths(-1)).DayOfYear;

return intJulianDate.ToString();

}

Hope it Helps

Fauzi ~4z

Execute a parameterised EXE dynamically inside SSIS

Hi Reader....

We had a requirement to run a same application for different POS(Point of sale) The challenge is to create a single application that takes parameters Store number as input parameter and does the required operation. Doing so we can use the same applicaiton any number of stores. In SSIS we have the module "Execute Process Task" to exectue exe files. I have given the file location in "Executalbe" property under Process Tab.



Regarding the Parameter, First i tried with 'args[0]' in the EXE Created from DotNet Console Applcaition, tried to use it as input parameters. Unfortunately it did'nt work. When i googled found that we can use Console.ReadLine() and get the required parameters in the EXE file And when you need to execute through SSIS(SQL Server Integration Services), we just need to pass the parameter (User Varaiable in SSIS, Eg: User::VarialbeName) in Standard Input Varaible property under Process.

This will pass the parameter dynamically during execution :-)

Regards
Fauzi

C# method to convert Julian to Gregorian date

Hello there,

I was in urgent need to convert Julian date to Georgian for a asp.net application which reads data from DB2.

The following code was useful.

public static string JulianToDateTime(int julianDate)

{

int RealJulian = julianDate + 1900000;

int Year = Convert.ToInt32(RealJulian.ToString().Substring(0, 4));

int DoY = Convert.ToInt32(RealJulian.ToString().Substring(4));

DateTime dtOut = new DateTime(Year, 1, 1);

return dtOut.AddDays(DoY - 1).ToShortDateString();

}

Hope It Helps

Regards
Fauzi

How to run a DTSX Programmatically through C#

Hey there...

For a data integration service, I create four DTSX packages. These were generic packages which needs to be executed one after the another. I tried using SQL Server Agent to schedule it as a JOB which has four steps to be executed sequentially. Unfortunately the package which is working fine is visual studio is not working when scheduled. There seems to be some permission issues(had the same issue few days before). As i had users waiting the see the output. I have to find an alternative solution soon. Here is the C# code to run the DTSX from a EXEC file.

Code:

Package pkg;

Application app;

DTSExecResult pkgResults;

Variables varStore;

pkgLocation = @"c:\documents and settings\Path\Filename.dtsx";

app = new Application();

pkg = app.LoadPackage(pkgLocation, null);

varStore = pkg.Variables;

varStore[YourVariable].Value = 123;

pkgResults = pkg.Execute();

Required NameSpace: The reference of Microsoft.SQLServer.ManagedDTS.dll has to be added and in code -

using Microsoft.SqlServer.Dts.Runtime;

Hope it helps :)

How to embed image in C# Email

Hi...

I had an interesting requirement to generate daily reports based on the accumulated sales data. And this Email will be sent to the respective Managers. Thought it would give a better picture if the email have the appropriate Brand images embedded on necessary places in report.

Following code was very much useful to embed an image in a system generated e-mail through C#.

Code:

Table objTable = new Table();

objTable.BorderStyle = BorderStyle.Solid;

objTable.BorderWidth = 1;

objTable.GridLines = GridLines.Both;

objTable.CellPadding = 5;

objTable.CellSpacing = 1;

objTable.BorderColor = System.Drawing.ColorTranslator.FromHtml("#035681");

objTable.Style.Add("Font-family", "Verdana");

objTable.Style.Add("Font-size", "14");

//Welcome TABLE CODE:

TableRow objTableRow_Welcome = new TableRow();

TableCell objTableCell_welcome1 = new TableCell();

objTableCell_welcome1.ColumnSpan = 1;

// Embeddind Image ID which is created below.

objTableCell_welcome1.Text = "<img src=\"cid:SampleImage\">";

objTableRow_Welcome.Cells.Add(objTableCell_welcome1);

objTable.Rows.Add(objTableRow_Welcome);

SmtpClient smtp = new SmtpClient("YOUR SMTP CLIENT");

MailAddress sender = new MailAddress(" FAUZI @ CHENNAI.com", "Fauzi");

MailAddress recipient = new MailAddress(" FAUZI @ INDIA.com", "Recipient");

MailMessage m = new MailMessage(sender, recipient);

m.Subject = "Embed image with C# mail";

// Define the plain text alternate view and add to message

string plainTextBody = "Best viewed in Email client(Outlook) that supports HTML messages";

AlternateView plainTextView =

AlternateView.CreateAlternateViewFromString(

plainTextBody, null, MediaTypeNames.Text.Plain);

m.AlternateViews.Add(plainTextView);

//Rendering Text from Created HTML TABLE in runtime

StringBuilder htmlBody = new StringBuilder();

StringWriter objStringWriter = new StringWriter(htmlBody);

HtmlTextWriter objHtmlTextWriter = new HtmlTextWriter(objStringWriter);

objTable.RenderControl(objHtmlTextWriter);

//Creating an Alternate view

AlternateView htmlView = AlternateView.CreateAlternateViewFromString(htmlBody.ToString(), null, MediaTypeNames.Text.Html);

// Embedding Image here

LinkedResource sampleImage = new LinkedResource("logo.gif", MediaTypeNames.Image.Jpeg);

sampleImage.ContentId = "SampleImage";

htmlView.LinkedResources.Add(sampleImage);

//Adding the created Alternate view

m.AlternateViews.Add(htmlView);

// Finally, configure smtp or alternatively use the

// system.net mailSettings

smtp.Send(m);

Hope it helps...

Please remember to give appropriate path of the image file.

Best Regards

Fauzi