¾È³çÇϼ¼¿ä. DB2 ¹× XpertMon »ç¿ëÀÚ ¿©·¯ºÐ!
¢ß ¾ÆÀÌƼ¿¢½ºÆÛÆ®±×·ì DB »ç¾÷ºÎÀÔ´Ï´Ù.
À̹ø¿¡´Â ADO.NET°ú Cloudscape¿¡ ´ëÇؼ »ìÆ캸µµ·Ï ÇÏ°Ú½À´Ï´Ù.
Å©°Ô ADO.NETÀÇ °³¿ä, ¼ÒÇÁÆ®¿þ¾î ¿ä±¸»çÇ×, ¼³Ä¡¹æ¹ý°ú ¿¹¿¡ ´ëÇؼ ´Ù·ç°Ú½À´Ï´Ù.
±×·³ ¿À´Ãµµ Áñ°Å¿î ÇÏ·ç µÇ½Ê½Ã¿À.
ADO.NET and Cloudscape
°³¿ä
DB2 Run-time ClientÀÇ ODBC Áö¿øÀ» ÅëÇØ Cloudscape³ª Derby¸¦ »ç¿ëÇؼ ÀÛ¾÷ÇÏ´Â
°³¹ßÀÚ´Â Java ÇÁ·Î±×·¥ ¿Ü¿¡µµ ODBC¸¦ ÅëÇØ C/C++ À̳ª Perl, .Net µîÀÇ ¾ð¾î·Î
µ¥ÀÌÅÍ¿¡ Á¢±ÙÇÒ ¼ö ÀÖ½À´Ï´Ù.
ÀÌ ´º½º·¹ÅÍ¿¡¼´Â DB2 Run-time Client¿Í ODBC.NET Data Provider¸¦ »ç¿ëÇؼ
IBM Cloudscape Ver.10.0 °ú Apache Derby µ¥ÀÌÅͺ£À̽ºÀÇ µ¥ÀÌÅÍ¿¡ Á¢¼ÓÇÏ´Â ¹æ¹ýÀ»
»ìÆ캸°Ú½À´Ï´Ù.
ADO.NETÀº ADO/OLE DB ÀÌÈÄ ¸¶ÀÌÅ©·Î¼ÒÇÁÆ®»çÀÇ Ãֽűâ¼úÀÔ´Ï´Ù. ADO.NETÀº XMLÅëÇÕ,
disconnected data access, Çâ»óµÈ update control µîÀ¸·Î ±¸¼ºµÈ ¸¶ÀÌÅ©·Î¼ÒÇÁÆ®
´å³ÝÀ» À§ÇÑ µ¥ÀÌÅÍ ¿¢¼¼½º ÀÎÅÍÆäÀ̽º ¹× ¶óÀ̺귯¸® ¼¼Æ®ÀÔ´Ï´Ù.
ADO.NET object modelÀº .NET Data Provider¿Í Data SetÀ¸·Î ¾Ë·ÁÁø 'Connected' ¹×
'Disconnected' ¿ÀºêÁ§Æ®ÀÇ ¼¼Æ®·Î ±¸¼ºµË´Ï´Ù.
.NET Data Provider´Â .NET application°ú ±â¹Ý µ¥ÀÌÅͼҽº »çÀÌÀÇ µ¥ÀÌÅÍ µå¶óÀ̹ö·Î
µ¿ÀÛÇϸç, µ¥ÀÌÅÍ °»½ÅÀ̳ª result set Á¶È¸µî°ú °°Àº µ¥ÀÌÅÍ ÀÛ¾÷À» ¼öÇàÇϱâ À§ÇÑ
connectionÀ» ±¸ÃàÇØ ÁÝ´Ï´Ù.
ODBC.NET Data Provider ´Â ODBC µå¶óÀ̹ö¸¦ ÅëÇØ .NET applicationÀÌ ODBC µ¥ÀÌÅÍ ¼Ò½º¿¡
Á¢±ÙÇÏ°Ô ÇÏ´Â °ü¸® ÄÄÆ÷³ÍÆ® ÀÔ´Ï´Ù. IBM ODBC µå¶óÀ̹ö´Â DB2 Run-time Client¿Í
ÇÔ²² ¹¿© ADO.NETÀÌ Cloudscape/Derby¸¦ Áö¿øÇϵµ·Ï Çϴµ¥ ÇÊ¿äÇÕ´Ï´Ù. .NET ÇÁ·¹ÀÓ¿öÅ©
¹öÀü 1.1¿¡¼, ODBC.NET Data Provider´Â ÇÁ·¹ÀÓ¿öÅ©¿¡ Æ÷ÇԵǾî ÀÖÀ¸¸ç, namespace´Â
System.Data.ODBCÀÔ´Ï´Ù.
.NET ÇÁ·¹ÀÓ¿öÅ© ¹öÀü 1.0¿¡¼´Â, .NET Data Provider´Â º°µµ·Î Á¦°øµÇ¸ç namespace´Â
Microsoft.Data.Odbc ÀÔ´Ï´Ù.
Data SetÀº .NET applicationÀ» À§ÇÑ ¿ÀÇÁ¶óÀÎ µ¥ÀÌÅÍ Ä³½Ã·Î µ¿ÀÛÇÕ´Ï´Ù.
application¿¡ ÀÇÇØ ÀÌ·ç¾îÁø update´Â ADO.NET ¸Þ¼Òµå¸¦ ÅëÇØ in-memory cache
data set °ú ±â¹Ý µ¥ÀÌÅÍ ¼Ò½º°£¿¡ Àü¼ÛµË´Ï´Ù.
IBM Cloudscape´Â ¿ÀÇ ¼Ò½º µ¥ÀÌÅͺ£À̽ºÀÎ Apache DerbyÀÇ IBMÀÇ »ó¾÷¿ë ¹èÆ÷ÆÇÀÔ´Ï´Ù.
¼ÒÇÁÆ®¿þ¾î ¿ä±¸»çÇ×
ADO.NETÀ» Áö¿øÇϱâ À§ÇÑ Cloudscape ´Â ´ÙÀ½À» ÇÊ¿ä·Î ÇÕ´Ï´Ù.
- Apache Derby ¶Ç´Â IBM Cloudscape ¹öÀü 10.0 ¶Ç´Â »óÀ§ ¹öÀü
- IBM DB2 runtime client ¿Í IBM ODBC Driver ¹öÀü 8.2 ¶Ç´Â »óÀ§ ¹öÀü
- .NET Framework Data Providers ? µ¥ÀÌÅͺ£À̽º¿¡ Á¢¼Ó, ¸í·É ½ÇÇà, °á°ú Á¶È¸µî¿¡ »ç¿ëµË´Ï´Ù.
- System.Data.Odbc Namespace - .NET Framework Data Provider for ODBC¿¡ »ç¿ëµÇ´Â namespace
- DB2 JDBC Universal Driver
¼³Ä¡
ODBC.NET Data Provider ¸¦ ÅëÇØ Cloudscape/Derby ¿¡¼ ADO.NET Áö¿øÀ» ¼³Ä¡Çϱâ À§Çؼ´Â,
¿ì¼±Cloudscape/Derby µ¥ÀÌÅͺ£À̽º¸¦ ODBC µ¥ÀÌÅÍ ¼Ò½º·Î µî·ÏÇØ¾ß ÇÕ´Ï´Ù.
- Cloudscape / Derby ³×Æ®¿öÅ© ¼¹ö¸¦ ±âµ¿ÇÕ´Ï´Ù.
java org.apache.derby.drda.NetworkServerControl start |
- ij ¸¦ ÅëÇØ Cloudscape / Derby ³×Æ®¿öÅ© ¼¹ö¿¡ Á¢¼ÓÇؼ µ¥ÀÌÅͺ£À̽º¸¦ »ý¼ºÇÕ´Ï´Ù.
¿¹¸¦ µé¸é ´ÙÀ½°ú °°ÀÌ ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.
java org.apache.derby.tools.ij
ij> connect 'jdbc:derby:net://localhost:1527/SAMPLEDB;
create=true:user=abc;password=abc;'; |
ÀÌ ¿¹¿¡¼´Â SAMPLEDB ¶ó´Â Cloudscape/Derby µ¥ÀÌÅͺ£À̽º¸¦ »ý¼ºÇÕ´Ï´Ù.
¶ÇÇÑ µ¥ÀÌÅͺ£À̽º¿¡ Á¢¼ÓÇϱâ À§Çؼ´Â IBM DB2 Universal JDBC Driver for
Cloudscape/Derby °¡ ¼³Ä¡µÇ¾î ÀÖ¾î¾ß ÇÕ´Ï´Ù.
- DB2 ¸í·ÉâÀ» ÅëÇØ Cloudscape / Derby ³×Æ®¿öÅ© ¼¹ö¸¦ DB2 run-time client¿¡ Ä«Å»·Î±× ÇÕ´Ï´Ù
db2 catalog tcpip node CNS remote localhost server 1527
db2 catalog db SAMPLEDB at node CNS authentication server |
- DB2 ¸í·Éâ¿¡¼ Á¢¼ÓÅ×½ºÆ®¸¦ ÇÕ´Ï´Ù.
db2 connect to SAMPLEDB user abc using abc |
- ODBC µ¥ÀÌÅÍ ¼Ò½º¸¦ »ý¼ºÇÕ´Ï´Ù. ODBC µ¥ÀÌÅͼҽº ¸í(DSN) Àº µ¥ÀÌÅͺ£À̽º¿¡
Á¢±ÙÇϱâ À§ÇÑ Á¢¼ÓÁ¤º¸¸¦ Æ÷ÇÔÇÏ°í ÀÖ½À´Ï´Ù.
DSNÀº ODBC Data Source Administrator¸¦ ÅëÇØ µÎ °¡Áö ¹æ¹ýÀ¸·Î »ý¼º ¹× ¼³Á¤ÀÌ °¡´ÉÇÕ´Ï´Ù.
- ¸¶ÀÌÅ©·Î¼ÒÇÁÆ® À©µµ¿ì 2000 À̳ª À©µµ¿ì XP¿¡¼, ODBC µ¥ÀÌÅÍ ¼Ò½º´Â ´ÙÀ½À» Ŭ¸¯ÇÔÀ¸·Î½á »ý¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù.
½ÃÀÛ -> ¼³Á¤ -> Á¦¾îÆÇ -> °ü¸®µµ±¸ -> µ¥ÀÌÅÍ ¿øº»(ODBC) -> ½Ã½ºÅÛ DSN |
IBM ODBC Driver¸¦ ¼±ÅÃÇÏ°í DSNÀ¸·Î ¼³Á¤ÇÒ Ä«Å»·Î±×µÈ µ¥ÀÌÅÍ º£À̽º¸¦ ¼±ÅÃÇÕ´Ï´Ù.
- DB2 ¸í·É â¿¡¼ ODBC µ¥ÀÌÅÍ ¼Ò½º¸¦ »ý¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù.
db2 catalog system odbc data source SAMPLEDB |
- SAMPLEDB¿¡ ´ëÇÑ ODBC µ¥ÀÌÅÍ ¼Ò½º°¡ »ý¼ºµÇ¾ú´ÂÁö È®ÀÎÇÕ´Ï´Ù.
db2 list system odbc data sources |
SAMPLEDB µ¥ÀÌÅÍ ¼Ò½º¸í°ú IBM DB2 ODBC DRIVER¶ó´Â À¯ÇüÀÌ Ç¥½ÃµÇ¾î¾ß Á¤»óÀÔ´Ï´Ù.
³ëµå³ª µ¥ÀÌÅͺ£À̽º°¡ ÀÌ¹Ì Ä«Å»·Î±× µÇ¾î ÀÖ´Ù¸é À§ÀÇ ¸í·ÉµéÀº ½ÇÆÐÇÒ °ÍÀÔ´Ï´Ù.
±×·± °æ¿ì, ³ëµå³ª µ¥ÀÌÅͺ£À̽º¸¦ uncatalog ÇÑ µÚ ´Ù½Ã ¼öÇàÇØ º¸½Ã±â ¹Ù¶ø´Ï´Ù.
db2 uncatalog node CNS
db2 uncatalog db SAMPLEDB |
ODBC°¡ Cloudscape/Derby µ¥ÀÌÅͺ£À̽º¿¡ Á¢¼Ó°¡´ÉÇÑÁö È®ÀÎÇϱâ À§ÇØ À©µµ¿ìÀÇ
ODBC µ¥ÀÌÅÍ ¼Ò½º ÅøÀ» ÀÌ¿ëÇؼ SAMPLEDB¸¦ ±¸¼ºÇÒ ¼öµµ ÀÖ½À´Ï´Ù.
À©µµ¿ì XP¿¡¼ Åø¿¡ Á¢±ÙÇϱâ À§Çؼ´Â
½ÃÀÛ -> Á¦¾îÆÇ -> °ü¸®µµ±¸ -> µ¥ÀÌÅÍ ¿øº»(ODBC)À¸·Î À̵¿ÇÕ´Ï´Ù.
ÀÌ·¸°Ô Çϸé ODBC µ¥ÀÌÅÍ ¿øº» °ü¸®ÀÚ Ã¢À¸·Î À̵¿ÇÏ°Ô µË´Ï´Ù.
¸¸¾à SAMPLEDB µ¥ÀÌÅÍ ¼Ò½º¸¦ Á¤»óÀûÀ¸·Î Ä«Å»·Î±× Çß´Ù¸é ½Ã½ºÅÛ DSN ÅÇÀ» ¼±ÅÃÇßÀ» ¶§
¸ñ·Ï¿¡¼ ±× ³»¿ëÀ» È®ÀÎ ÇÒ ¼ö ÀÖ½À´Ï´Ù.
SAMPLEDB À̸§À» ¼±ÅÃÇÏ°í, ±¸¼º( C ) ¹öÆ°À» Ŭ¸¯ÇÕ´Ï´Ù.
±×·¯¸é ¾Æ·¡¿Í °°Àº âÀÌ ³ªÅ¸³¯ °ÍÀÔ´Ï´Ù.
<±×¸² 1. Derby µ¥ÀÌÅÍ ¼Ò½º¿¡ ODBC Á¢¼Ó È®ÀÎ>
Cloudscape/Derby ³×Æ®¿öÅ© ¼¹ö°¡ localhost¿¡¼ µ¹¾Æ°¡°í ÀÖ´Ù¸é, »ç¿ëÀÚ ID ÀÔ·Ââ°ú
¾ÏÈ£ÀÔ·ÂâÀ» ä¿ì°í ¿¬°á( C ) ¹öÆ°À» Ŭ¸¯Çؼ Cloudscape/Derby µ¥ÀÌÅͼҽºÀÎ
SAMPLEDB¿¡ Á¢¼ÓÀ» Å×½ºÆ®ÇÕ´Ï´Ù. Á¢¼Ó¿¡ ¼º°øÇϸé Æ˾÷ âÀÌ ³ªÅ¸³¯ °ÍÀÔ´Ï´Ù.
- À§ÀÇ °úÁ¤ÀÌ ¼º°øÀûÀ¸·Î ³¡³ª°í ³ª¸é, ODBC .NET Data Provider¸¦ Æ÷ÇÔÇÑ
.NET Framework Version 1.1À» ¼³Ä¡ ÇϽʽÿÀ. ¸¸¾à .NET Framework Version 1.0À» »ç¿ëÇÒ
°æ¿ì¿£ ODBC.NET Data Provider¸¦ º°µµ·Î ´Ù¿î·Îµå ¹Þ¾Æ¾ß ÇÕ´Ï´Ù.
Examples
¾Æ·¡ÀÇ ¿¹Á¦ÄÚµå´Â ODBC.NET Data Provider¸¦ ÀÌ¿ëÇؼ Cloudscape/Derby µ¥ÀÌÅͺ£À̽º¿¡
Á¢¼ÓÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù. Àüü C# ¿¹Á¦Äڵ带 ÄÄÆÄÀÏÇϱâ À§Çؼ´Â
.NET Framework Version 1.1À» ¼³Ä¡ÇÏ°í, C# ÄÄÆÄÀÏ·¯ÀÎ csc.exe¸¦ »ç¿ëÇϽʽÿÀ.
¿¹¸¦µé¾î ´ÙÀ½ ¸í·ÉÀ» »ç¿ëÇϸé Sample1.cs ¶ó´Â C# ¼Ò½ºÆÄÀÏÀ» ÄÄÆÄÀÏÇؼ
Sample1.exe¶ó´Â ¸í·Éâ applicationÀ» »ý¼ºÇÕ´Ï´Ù.
csc /target:exe Sample1.cs |
¡®abc¡¯ ¶ó´Â »ç¿ëÀÚ ID¿Í Æнº¿öµå·Î SAMPLEDB¿¡ Á¢¼ÓÇÕ´Ï´Ù.
String connString = "DSN=SAMPLEDB;UID=abc;PWD=abc;";
// where datasource name is SAMPLEDB
OdbcConnection conn = new OdbcConnection(connString);
conn.Open(); |
Á¢¼Ó ÈÄ, Æ®·£Àè¼ÇÀ» ½ÃÀÛ, ¼öÇà ¹× COMMIT ÇÕ´Ï´Ù.
// conn is an OdbcConnection instance
OdbcCommand cmd = conn.CreateCommand();
OdbcTransaction trans = conn.BeginTransaction(); // start a transaction
cmd.Transaction = trans;
cmd.CommandText = "UPDATE staff " + " SET salary = (SELECT MIN(salary) " +
" FROM staff " + " WHERE id >= 310) " + " WHERE id = 310";
cmd.ExecuteNonQuery(); // execute the statement
trans.Commit(); // commit the transaction |
½ºÅä¾îµå ÇÁ·Î½ÃÀúµµ È£ÃâÇÒ ¼ö ÀÖ½À´Ï´Ù.
// conn is an OdbcConnection instance
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "CALL IN_PARAM(?)";
// Register input parameter for the OdbcCommand
cmd.Parameters.Add(new OdbcParameter("@p1", OdbcType.Int));
cmd.Parameters[0].Value = 100;
// Call the stored procedure
cmd.ExecuteNonQuery(); |
Äõ¸® °á°ú¸¦ Àо´Ï´Ù.
// conn is an OdbcConnection instance
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT sid, name FROM staff WHERE sid > 100";
OdbcDataReader reader = cmd.ExecuteReader();
Int32 sid = 0;
String name = "";
// Output the results of the query
while(reader.Read()) {
sid = reader.GetInt32(0); // first column
name = reader.GetString(1); // second column
Console.WriteLine(" " + sid + " " + name);
}
reader.Close(); |
¾Æ·¡ ¿¹´Â Cloudscape/Derby µ¥ÀÌÅͺ£À̽º¿¡ Á¢¼ÓÇؼ Å×À̺íÀ» »ý¼ºÇÏ°í µ¥ÀÌÅ͸¦
¸î °Ç insert ÇÑ ÈÄ, ³»¿ëÀ» fetchÇؼ º¸¿©ÁÖ´Â ³»¿ëÀÔ´Ï´Ù.
// Note: This C# example uses .NET Framework v1.1
// It connects to a Cloudscape/Derby database named SAMPLEDB.
// It creates a table called staff, inserts 3 rows, then fetches
// and displays the result set on the console.
using System;
using System.Data;
using System.Data.Odbc;
namespace IBM.Cloudscape.Samples
{
class Sample1
{
[STAThread]
static void Main(string[] args)
{
//String connString = @"Driver={IBM DB2 ODBC
//DRIVER};DBALIAS=SAMPLEDB;UID=abc;PWD=abc";
String connString = @"DSN=SAMPLEDB;UID=abc;PWD=abc"; // using DSN
OdbcConnection conn = null;
OdbcCommand cmd = null;
Console.WriteLine("Connecting to SAMPLEDB");
try
{
conn = new OdbcConnection(connString);
// open database connection
conn.Open();
Console.WriteLine("Connected to SAMPLEDB");
// creates a table named staff
cmd = new OdbcCommand("CREATE TABLE staff
(sid INT PRIMARY KEY NOT NULL, name VARCHAR(30)
NOT NULL, salary INT NOT NULL)", conn);
cmd.ExecuteNonQuery();
Console.WriteLine("Table staff created");
// insert data to table staff
cmd.CommandText = "INSERT INTO staff VALUES (300, 'John', 35000)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO staff VALUES (310, 'Mary', 55000)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO staff VALUES (320, 'Tom', 35000)";
cmd.ExecuteNonQuery();
Console.WriteLine("3 rows inserted");
// Fetch data from table staff
cmd = new OdbcCommand("SELECT * FROM staff", conn);
OdbcDataReader rdr = cmd.ExecuteReader();
Console.WriteLine("Fetching rows from staff");
while (rdr.Read())
{
Console.Write(rdr.GetInt32(0));
Console.Write(" " + rdr.GetString(1) + " ");
Console.WriteLine(rdr.GetInt32(2));
}
rdr.Close();
}
catch (OdbcException ex)
{
int cnt = ex.Errors.Count;
for (int i=0; i < cnt; i++)
{
Console.WriteLine("Error #" + i + "
" +
"Message: " + ex.Errors[i].Message + "
" +
"Native: " + ex.Errors[i].NativeError.ToString() + "
" +
"SQL: " + ex.Errors[i].SQLState + "
");
}
}
finally
{
if (conn != null)
conn.Close();
Console.WriteLine("Sample1 ended");
}
}
}
} |
XpertMon for DB2 UDB V2
DB »ç¾÷ºÎ
Tel : 02-2108-1458
Fax : 02-2108-1459
Mobile : 011-896-6545
E-mail :
hjlee@iteg.co.kr
URL :
http://iteg.co.kr