AS400 DB2 - JackHu88/Comm GitHub Wiki
https://blog.csdn.net/weixin_30828379/article/details/97786650
1.安装DbVisualizer
2.下载JTopen 里面会有jt400.jar http://sourceforge.net/projects/jt400/files/latest/download
3.Tools -> Drive Manager 设置DB2 for AS/400(JTOpen),给出db2jcc4.jar和jt400.jar路径，然后连接
C:\Program Files\DbVisualizer\jdbc\db2\db2jcc4.jar
iSeries Client Access V6R1
ODBC Data Source Administrator-->
Add-->iSeries Access ODBC Driver-->
Configure Data Source Name, Select System (Server) -->
Click Connection options-->Input User ID-->
Click Server Tab-->Input SQL Default Library (Database)
//Dsn configured in ODBC Data Source Administrator
string connectionString = @"Dsn=" + Dsn + ";uid=" + uid + ";pwd=" + pwd;
string deleteString = "delete from \"server\".\"database\".\"table\" ";
deleteString += " WHERE DATE(RAMAINDATE) >= DATE(CURRENT DATE - 3 DAYS)";
string insertString = "INSERT INTO \"server\".\"database\".\"table\" (col1,col2,col3)";
insertString += " VALUES (?,?,?)";
string updateString = "IF EXISTS (SELECT col1 FROM \"server\".\"database\".\"table\" WHERE col1='" + col1+ "') ";
updateString += " "UPDATE \"server\".\"database\".\"table\" SET col1=?,col2=?,col3=?";
updateString += " ELSE INSERT INTO \"server\".\"database\".\"table\" (col1,col2,col3)";
updateString += " VALUES (?,?,?)";
using (OdbcConnection DB2Connection = new OdbcConnection(connectionString))
{
//delete
OdbcCommand command = new OdbcCommand(deleteString, DB2Connection);
DB2Connection.Open();
try
{
int rows = command.ExecuteNonQuery();
Console.WriteLine("Remove Data", "Remove Data Items:" + rows);
}catch(Exception err){
Console.WriteLine("Remove Data: ", err.Message);
}
//update
string Modified = Modified_Date.ToString("yyyy-MM-dd-HH:mm:ss.FFFFFF");
try
{
string strModified = Modified.Replace("-", "").Replace(":", "").Substring(0, 14);
string updateString = "UPDATE \"server\".\"database\".\"table\" SET RAMAINDATE='" + strModified + "' WHERE col1='test1' AND col2='test2' ";
command = new OdbcCommand(updateString, DB2Connection);
int rows = command.ExecuteNonQuery();
if (rows == 0)
{
insert = true;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//new
string Modified = Modified_Date.ToString("yyyy-MM-dd-HH:mm:ss.FFFFFF");
try
{
command = new OdbcCommand(insertString, DB2Connection);
command.CommandTimeout = 0;
command.Parameters.AddWithValue("col1", "");
command.Parameters.AddWithValue("col2", "");
command.Parameters.AddWithValue("cll3", Convert.ToInt32(Modified));
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
https://stackoverflow.com/questions/2374698/parameterized-db2-query-from-net
https://www.idug.org/p/fo/et/thread=33357
string connectionString = @"Driver={Client Access ODBC Driver (32-bit)};System=" + server +;UID=" + uid + ";PWD=" + pwd + ";NAM=1;";
//string connectionString = @"Driver={IBM DB2 ODBC DRIVER};System=" + server + ";UID=" + uid + ";PWD=" + pwd + ";NAM=1;";
using (OdbcConnection DB2Connection = new OdbcConnection(connectionString))
{
}
string connectionString = @"Provider=IBMDA400;Data Source=" + server + ";User Id=" + uid + ";Password=" + pwd;
using (OleDbConnection DB2Connection = new OleDbConnection(connectionString)){
}
https://stackoverflow.com/questions/330241/does-db2-have-an-insert-or-update-statement
update TABLE_NAME set FIELD_NAME=xxxxx where MyID=XXX;
INSERT INTO TABLE_NAME values (MyField1,MyField2)
WHERE NOT EXISTS(select 1 from TABLE_NAME where MyId=xxxx);
https://www.cnblogs.com/equation/articles/9193999.html
MERGE INTO table_to_upsert AS tab
USING (VALUES
(1, 2, 3),
(4, 5, 6),
(7, 8, 9)
-- more rows
) AS merge (C1, C2, C3)
ON tab.key_to_match = merge.key_to_match
WHEN MATCHED THEN
UPDATE SET tab.C1 = merge.C1,
tab.C2 = merge.C2,
tab.C3 = merge.C3
WHEN NOT MATCHED THEN
INSERT (C1, C2, C3)
VALUES (merge.C1, merge.C2, merge.C3)
#region 获取字符串在400中存储实际占用的长度
/// <summary>
/// 获取字符串在400中存储实际占用的长度
/// creator: zhengjx
/// </summary>
/// <param name="strInput"></param>
/// <returns></returns>
public static int LengthOfAS400String(string strInput)
{
///400中文字符规则：中文字符串前面应该有前导符，后面紧跟后导符
///所以总长度应该 = strInput.Length + 中文字符数 + 中文字符串数*2
int rtnLength = strInput.Length;
Regex regex = new Regex(@"[\u0100-\uffff]+");
MatchCollection matches = regex.Matches(strInput);
for( int i = 0; i != matches.Count; ++i )
{
rtnLength += matches[i].Value.ToString().Trim().Length + 2;
}
return rtnLength;
#region 另外一种方法：遍历字符串来判断（注释）
// int rtnLength = 0;
// if (HasChinese(strInput))
// {
// char[] cArr = strInput.ToCharArray();
// bool isNextCharChinese = false;
//
// for(int i = 0; i<cArr.Length ; i++)
// {
// if ( i== cArr.Length - 1)
// {
// //最后一个字符如果是中文，应该+3 （中文2个字符+一个后导符）
// if (HasChinese(cArr[i].ToString().Trim()))
// {
// rtnLength += 3;
// //第一个字符如果是中文，应该多加一个前导符
// if ( i == 0)
// {
// rtnLength += 1;
// }
// }
// else
// {
// rtnLength += 1;
// }
// continue;
// }
//
// isNextCharChinese = HasChinese(cArr[i+1].ToString().Trim());
//
// if (HasChinese(cArr[i].ToString().Trim()))
// {
// //第一个字符如果是中文，应该多加一个前导符
// if ( i == 0)
// {
// rtnLength += 1;
// }
// rtnLength += 2;
// //如果当前字符是中文，且下一个字符是英文，则应多加一个后导符
// if (!isNextCharChinese)
// {
// rtnLength += 1;
// }
// }
// else
// {
// rtnLength += 1;
// if (isNextCharChinese)
// {
// rtnLength += 1;
// }
// }
// }
//
// }
// else
// {
// rtnLength = strInput.Length;
// }
// return rtnLength;
#endregion
}
#endregion
#region 获取字符串的前几位（按字符串在400中存储的实际长度计算）
/// <summary>
/// 获取字符串的前几位（按字符串在400中存储的实际长度计算）
/// </summary>
/// <param name="strInput"></param>
/// <param name="length"></param>
/// <param name="strLeave"></param>
/// <returns></returns>
public static string SubStringFOR400(string strInput,int length,ref string strLeave)
{
if (LengthOfAS400String(strInput) <= length)
{
strLeave = "";
return strInput;
}
for(int i = strInput.Length ;i > 0;i--)
{
if (LengthOfAS400String(strInput.Substring(0,i)) <= length)
{
strLeave = strInput.Substring(i,strInput.Length - i);
return strInput.Substring(0,i);
}
}
strLeave = strInput;
return "";
}
#endregion