C# - 닷넷 응용 프로그램에서 DB2 Express-C 데이터베이스 사용 (3) - ibmcom/db2express-c 컨테이너 사용
지난 글에서는,
C# - 닷넷 응용 프로그램에서 DB2 Express-C 데이터베이스 사용 (2)
; https://www.sysnet.pe.kr/2/0/1408
윈도우에 생성한 DB2 환경을 예로 들었는데 이번에는 docker에 이미 설치된 ("ibmcom/db2express-c"에서 이름이 바뀐) "ibmcom/db2" 컨테이너로 예를 들겠습니다. 이를 위해 db2 환경이 실행 중인 컨테이너 내에 bash를 실행시켜 들어갑니다.
# docker ps | grep db2
a3cf35df65ba db2_db "/entrypoint.sh db2s…" 5 months ago Up 13 days 22/tcp, 0.0.0.0:50000->50000/tcp db2_db_1
// ibmcom/db2express-c 이미지가 실행 중인 컨테이너 ID: a3cf35df65ba
# docker exec -it a3cf35df65ba /bin/bash
그래도 혹시 모르니 db2가 적절하게 설치되었는지 db2val을 찾아,
# find / -name db2val
/home/db2inst1/sqllib/bin/db2val
확인해 보고,
# /home/db2inst1/sqllib/bin/db2val
DBI1379I The db2val command is running. This can take several minutes.
DBI1335I Installation file validation for the DB2 copy installed at
/home/db2inst1/sqllib was successful.
DBI1343I The db2val command completed successfully. For details, see
the log file /tmp/db2val-200305_062706.log.
이후 db2 실행 파일을 찾아 DB 제어를 위한 명령행 환경으로 진입합니다.
# find / -name db2
/home/db2inst1/sqllib/bin/db2
...[생략]...
# /home/db2inst1/sqllib/bin/db2
...[생략]...
db2 =>
db2 명령행까지 들어왔다면 이제 게임 끝이군요, (아래의 예는 생성된 DB 목록을 조회하는 것으로) 여타 DB 관리처럼 이후 진행을 하면 됩니다.
db2 => list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = MYTESTDB
Database name = MYTESTDB
Local database directory = /home/db2inst1
Database release level = 10.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
여기까지 해서 컨테이너의 TCP 매핑, Database 이름을 구했으면 IBM.Data.DB2.dll의 DB2Connection 객체에 사용할 연결 문자열이 다음과 같이 구해집니다.
Server=testdbsrv:50000;Database=MYTESTDB;UID=...;PWD=...
이후의 제어는 그냥 편안하게 "
IBM Data Studio"를 이용해 원격에서 제어하시는 것도 ^^ 좋겠습니다.
이제 환경 구성하면서 오류가 발생한 상황을 정리해 보겠습니다. 우선, DB2Connection.Open에서 다음과 같은 오류가 발생한다면?
Server Error in '/' Application.
SQL1159 Initialization error with DB2 .NET Data Provider, reason code 2, tokens C:\Test\WebApp\bin\db2app64.dll, , Process Owner: TESTAD\TESTUSR
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: SQL1159 Initialization error with DB2 .NET Data Provider, reason code 2, tokens C:\Test\WebApp\bin\db2app64.dll, , Process Owner: TESTAD\TESTUSR
Source Error:
Line 48: {
Line 49: connection.ConnectionString = connectionString;
Line 50: connection.Open();
Line 51:
Source File: C:\Test\WebApp\DB2Default.aspx.cs Line: 50
Stack Trace:
[InvalidOperationException: SQL1159 Initialization error with DB2 .NET Data Provider, reason code 2, tokens C:\Test\WebApp\bin\db2app64.dll, , Process Owner: TESTAD\TESTUSR]
IBM.Data.DB2.DB2ConnPool.Open(DB2Connection connection, String& szConnectionString, DB2ConnSettings& ppSettings, Object& ppConn) +7637
IBM.Data.DB2.DB2Connection.Open() +577
WebApp.DB2Default.Page_Load(Object sender, EventArgs e) in C:\Test\WebApp\DB2Default.aspx.cs:50
System.Web.UI.Control.OnLoad(EventArgs e) +108
System.Web.UI.Control.LoadRecursive() +90
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1607
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.8.4075.0}
해당 프로젝트가 빌드된 /bin 폴더에 db2app64.dll 파일이 없기 때문입니다.
IBM Data Studio에서 db2 서버의 주소, 데이터베이스, 계정 명과 비밀번호를 입력 후 "Test Connection"을 했는데 다음과 같은 오류가 발생한다면?
A connection attempt was unsuccessful.
Summary
SQL4499N A fatal error occurred that resulted in a disconnect from the data source.
Try the following suggestions to help solve the problem:
Ensure that the data server has been started.
Check that the database name used is the actual database name and not an alias name.
Check that the host is a valid name or IP address, and that the port number is valid for communication with the data server.
To further diagnose the problem, return to the Properties section and use the Tracing page to enable tracing. Contact the database administrator and provide the trace and other support information available.
Full message content: [jcc][t4][2057][11264][4.14.113] 응용프로그램 서버(AS)가 연결을 거부했습니다.
찾을 수 없거나 트랜잭션을 지원하지 않은 TESTDB 데이터베이스에 액세스하려고 시도했습니다.
Explanation
<b>Explanation:</b><br/><br/>An attempt was made to access a database that was not found, has not been started, or does not support transactions.
<br/><b>User response:</b><br/><br/>Ensure that the specified database name exists in the system database directory. If the database name does not exist in the system database directory, either the database does not exist or the database name has not been cataloged. If needed, issue a db2start command and then resubmit the current command.
SQLSSTATE: 08004
Support
Connection Properties
Database : TESTDB
URL : jdbc:db2://192.168.100.50:50000/TESTDB:retrieveMessagesFromServerOnGetMessage=true;securityMechanism=4;
User name : TESTUSR
Password : (Not Shown)
Environment
Driver class name = com.ibm.db2.jcc.DB2Driver
Driver version = 4.14.113
Driver JAR file path = C:\Program Files\ibm\SDPShared\plugins\com.ibm.datatools.db2_2.1.503.v20121002_1809\driver\db2jcc4.jar;C:\Program Files\ibm\SDPShared\plugins\com.ibm.datatools.db2_2.1.503.v20121002_1809\driver\db2jcc_license_cisuz.jar
Operating system name = Windows Server 8
Operating system architecture = amd64
Operating system version = 6.2
JVM implementation vendor = IBM Corporation
JVM runtime = Java(TM) SE Runtime Environment
Java version = JRE 1.7.0 IBM J9 2.6 Windows Server 8 amd64-64 20120809_118929 (JIT enabled, AOT enabled)
J9VM - R26_Java726_SR2_20120809_0948_B118929
JIT - r11.b01_20120808_24925
GC - R26_Java726_SR2_20120809_0948_B118929
J9CL - 20120809_118929
Launcher = C:\Program Files\ibm\DS3.2.0\eclipse.exe
Stack Trace
com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2057][11264][4.14.113] 응용프로그램 서버(AS)가 연결을 거부했습니다.
찾을 수 없거나 트랜잭션을 지원하지 않은 TESTDB 데이터베이스에 액세스하려고 시도했습니다. ERRORCODE=-4499, SQLSTATE=08004com.ibm.db2.jcc.am.ed.a(Unknown Source)
com.ibm.db2.jcc.am.ed.a(Unknown Source)
com.ibm.db2.jcc.t4.y.u(Unknown Source)
com.ibm.db2.jcc.t4.y.n(Unknown Source)
com.ibm.db2.jcc.t4.y.a(Unknown Source)
com.ibm.db2.jcc.t4.y.a(Unknown Source)
com.ibm.db2.jcc.t4.b.c(Unknown Source)
com.ibm.db2.jcc.t4.b.b(Unknown Source)
com.ibm.db2.jcc.t4.b.b(Unknown Source)
com.ibm.db2.jcc.t4.b.a(Unknown Source)
com.ibm.db2.jcc.t4.b.a(Unknown Source)
com.ibm.db2.jcc.t4.b.a(Unknown Source)
com.ibm.db2.jcc.t4.b.<init>(Unknown Source)
com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(Unknown Source)
com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(Unknown Source)
com.ibm.db2.jcc.DB2Driver.connect(Unknown Source)
com.ibm.db2.jcc.DB2Driver.connect(Unknown Source)
org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(Unknown Source)
org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(Unknown Source)
org.eclipse.datatools.connectivity.DriverConnectionBase.open(Unknown Source)
org.eclipse.datatools.enablement.ibm.db2.internal.luw.JDBCLUWConnectionFactory.createConnection(Unknown Source)
org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(Unknown Source)
org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(Unknown Source)
com.ibm.datatools.connection.internal.ui.databases.NewDatabaseConnnectionWizardPage.handleTestConnection(Unknown Source)
com.ibm.datatools.connection.internal.ui.databases.NewDatabaseConnnectionWizardPage.access$6(Unknown Source)
com.ibm.datatools.connection.internal.ui.databases.NewDatabaseConnnectionWizardPage$10.handleEvent(Unknown Source)
org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
org.eclipse.jface.window.Window.runEventLoop(Unknown Source)
org.eclipse.jface.window.Window.open(Unknown Source)
com.ibm.datatools.connection.internal.ui.databases.actions.NewDBConnectionHandler.execute(Unknown Source)
org.eclipse.ui.internal.handlers.HandlerProxy.execute(Unknown Source)
org.eclipse.core.commands.Command.executeWithChecks(Unknown Source)
org.eclipse.core.commands.ParameterizedCommand.executeWithChecks(Unknown Source)
org.eclipse.ui.internal.handlers.HandlerService.executeCommand(Unknown Source)
org.eclipse.ui.internal.handlers.SlaveHandlerService.executeCommand(Unknown Source)
org.eclipse.ui.internal.handlers.SlaveHandlerService.executeCommand(Unknown Source)
org.eclipse.ui.menus.CommandContributionItem.handleWidgetSelection(Unknown Source)
org.eclipse.ui.menus.CommandContributionItem.access$19(Unknown Source)
org.eclipse.ui.menus.CommandContributionItem$5.handleEvent(Unknown Source)
org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
org.eclipse.ui.internal.Workbench.runEventLoop(Unknown Source)
org.eclipse.ui.internal.Workbench.runUI(Unknown Source)
org.eclipse.ui.internal.Workbench.access$4(Unknown Source)
org.eclipse.ui.internal.Workbench$7.run(Unknown Source)
org.eclipse.core.databinding.observable.Realm.runWithDefault(Unknown Source)
org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Unknown Source)
org.eclipse.ui.PlatformUI.createAndRunWorkbench(Unknown Source)
org.eclipse.ui.internal.ide.application.IDEApplication.start(Unknown Source)
org.eclipse.equinox.internal.app.EclipseAppHandle.run(Unknown Source)
org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(Unknown Source)
org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(Unknown Source)
org.eclipse.core.runtime.adaptor.EclipseStarter.run(Unknown Source)
org.eclipse.core.runtime.adaptor.EclipseStarter.run(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
org.eclipse.equinox.launcher.Main.invokeFramework(Unknown Source)
org.eclipse.equinox.launcher.Main.basicRun(Unknown Source)
org.eclipse.equinox.launcher.Main.run(Unknown Source)
org.eclipse.equinox.launcher.Main.main(Unknown Source)
데이터베이스 (위의 메시지에서는 TESTDB)가 실제로 있는지 확인할 필요가 있습니다.
또는, 다음과 같이 오류가 발생한다면?
A connection attempt was unsuccessful.
Summary
SQL4214N A connection authorization failure has occurred.
Try the following suggestions to help solve the problem:
Ensure that the user name and password are correct. These values are defined in the New Connection wizard or, for an existing connection, on the General page in the Driver Properties section.
Ensure that the user name is valid. For example, on UNIX and Linux platforms, user names must be all lowercase. <a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.dbobj.doc/doc/c0007245.html">Review the DB2 general naming rules</a>.
If you recently changed permissions on critical DB2 files, such as db2ckpw, or moved to a new fix pack, the db2iupdt command that updates the instance might not have been run. Contact your DBA to run db2iupdt with the InstName parameter to update the instance.
Full message content: [jcc][t4][201][11237][4.14.113] 연결 권한 부여에 실패했습니다. 이유: 보안 메커니즘이 지원되지 않습니다.
Explanation
<b>Explanation:</b><br/><br/>An error occurred during security processing. Either the user name specified or the password specified, or both, are invalid.
<br/><b>User response:</b><br/><br/>Ensure that the specified user name and password are correct.
SQLSSTATE: 28000
Support
Connection Properties
Database : MYTESTDB
URL : jdbc:db2://192.168.100.50:50000/MYTESTDB:retrieveMessagesFromServerOnGetMessage=true;securityMechanism=4;
User name : TESTUSR
Password : (Not Shown)
Environment
Driver class name = com.ibm.db2.jcc.DB2Driver
Driver version = 4.14.113
Driver JAR file path = C:\Program Files\ibm\SDPShared\plugins\com.ibm.datatools.db2_2.1.503.v20121002_1809\driver\db2jcc4.jar;C:\Program Files\ibm\SDPShared\plugins\com.ibm.datatools.db2_2.1.503.v20121002_1809\driver\db2jcc_license_cisuz.jar
Operating system name = Windows Server 8
Operating system architecture = amd64
Operating system version = 6.2
JVM implementation vendor = IBM Corporation
JVM runtime = Java(TM) SE Runtime Environment
Java version = JRE 1.7.0 IBM J9 2.6 Windows Server 8 amd64-64 20120809_118929 (JIT enabled, AOT enabled)
J9VM - R26_Java726_SR2_20120809_0948_B118929
JIT - r11.b01_20120808_24925
GC - R26_Java726_SR2_20120809_0948_B118929
J9CL - 20120809_118929
Launcher = C:\Program Files\ibm\DS3.2.0\eclipse.exe
Stack Trace
com.ibm.db2.jcc.am.SqlInvalidAuthorizationSpecException: [jcc][t4][201][11237][4.14.113] 연결 권한 부여에 실패했습니다. 이유: 보안 메커니즘이 지원되지 않습니다. ERRORCODE=-4214, SQLSTATE=28000com.ibm.db2.jcc.am.ed.a(Unknown Source)
com.ibm.db2.jcc.am.ed.a(Unknown Source)
com.ibm.db2.jcc.am.ed.a(Unknown Source)
com.ibm.db2.jcc.t4.b.f(Unknown Source)
com.ibm.db2.jcc.t4.b.a(Unknown Source)
com.ibm.db2.jcc.t4.y.b(Unknown Source)
com.ibm.db2.jcc.t4.y.a(Unknown Source)
com.ibm.db2.jcc.t4.y.a(Unknown Source)
com.ibm.db2.jcc.t4.b.c(Unknown Source)
com.ibm.db2.jcc.t4.b.b(Unknown Source)
com.ibm.db2.jcc.t4.b.b(Unknown Source)
com.ibm.db2.jcc.t4.b.a(Unknown Source)
com.ibm.db2.jcc.t4.b.a(Unknown Source)
com.ibm.db2.jcc.t4.b.a(Unknown Source)
com.ibm.db2.jcc.t4.b.<init>(Unknown Source)
com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(Unknown Source)
com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(Unknown Source)
com.ibm.db2.jcc.DB2Driver.connect(Unknown Source)
com.ibm.db2.jcc.DB2Driver.connect(Unknown Source)
org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(Unknown Source)
org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(Unknown Source)
org.eclipse.datatools.connectivity.DriverConnectionBase.open(Unknown Source)
org.eclipse.datatools.enablement.ibm.db2.internal.luw.JDBCLUWConnectionFactory.createConnection(Unknown Source)
org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(Unknown Source)
org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(Unknown Source)
com.ibm.datatools.connection.internal.ui.databases.NewDatabaseConnnectionWizardPage.handleTestConnection(Unknown Source)
com.ibm.datatools.connection.internal.ui.databases.NewDatabaseConnnectionWizardPage.access$6(Unknown Source)
com.ibm.datatools.connection.internal.ui.databases.NewDatabaseConnnectionWizardPage$10.handleEvent(Unknown Source)
org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
org.eclipse.jface.window.Window.runEventLoop(Unknown Source)
org.eclipse.jface.window.Window.open(Unknown Source)
com.ibm.datatools.connection.internal.ui.databases.actions.NewDBConnectionHandler.execute(Unknown Source)
org.eclipse.ui.internal.handlers.HandlerProxy.execute(Unknown Source)
org.eclipse.core.commands.Command.executeWithChecks(Unknown Source)
org.eclipse.core.commands.ParameterizedCommand.executeWithChecks(Unknown Source)
org.eclipse.ui.internal.handlers.HandlerService.executeCommand(Unknown Source)
org.eclipse.ui.internal.handlers.SlaveHandlerService.executeCommand(Unknown Source)
org.eclipse.ui.internal.handlers.SlaveHandlerService.executeCommand(Unknown Source)
org.eclipse.ui.menus.CommandContributionItem.handleWidgetSelection(Unknown Source)
org.eclipse.ui.menus.CommandContributionItem.access$19(Unknown Source)
org.eclipse.ui.menus.CommandContributionItem$5.handleEvent(Unknown Source)
org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
org.eclipse.ui.internal.Workbench.runEventLoop(Unknown Source)
org.eclipse.ui.internal.Workbench.runUI(Unknown Source)
org.eclipse.ui.internal.Workbench.access$4(Unknown Source)
org.eclipse.ui.internal.Workbench$7.run(Unknown Source)
org.eclipse.core.databinding.observable.Realm.runWithDefault(Unknown Source)
org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Unknown Source)
org.eclipse.ui.PlatformUI.createAndRunWorkbench(Unknown Source)
org.eclipse.ui.internal.ide.application.IDEApplication.start(Unknown Source)
org.eclipse.equinox.internal.app.EclipseAppHandle.run(Unknown Source)
org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(Unknown Source)
org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(Unknown Source)
org.eclipse.core.runtime.adaptor.EclipseStarter.run(Unknown Source)
org.eclipse.core.runtime.adaptor.EclipseStarter.run(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
org.eclipse.equinox.launcher.Main.invokeFramework(Unknown Source)
org.eclipse.equinox.launcher.Main.basicRun(Unknown Source)
org.eclipse.equinox.launcher.Main.run(Unknown Source)
org.eclipse.equinox.launcher.Main.main(Unknown Source)
접속 계정의 ID나 비밀번호를 다시 체크해 볼 필요가 있습니다.
[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]