天道酬勤,学无止境

Library List is not being used in remote PHP DB2 connection to IBM i

I've successfully connected to a remote IBM i DB2 database (AS400) from my local Windows PC via PHP. I'm using the IBM Data Server Client in conjunction with the db2_* functions in PHP. The problem I'm having is that despite my library list being set properly, it is not being used for unqualified table names. Instead it uses the current user name as the library. However, when I qualify the table names everything works like a charm.

I've confirmed that my library list is actually changing when I create the connection by querying QSYS2.LIBRARY_LIST_INFO.

$database = '<database name>';
$user = '<user name>';
$password = '<password';
$port = <port>;

$options['i5_naming'] = DB2_I5_NAMING_ON;
$options['autocommit'] = DB2_AUTOCOMMIT_OFF;
$options['i5_libl'] = 'MYLIB YOURLIB ANYLIB';

$conn = db2_connect($database, $user, $password, $options);

if ($conn) {
    echo "Connection succeeded."; //It succeeds

}
else {
    echo db2_conn_error()." | ".db2_conn_errormsg()."<br />";
    echo "Connection failed.";
}

$sql = "SELECT * FROM QSYS2.LIBRARY_LIST_INFO"; 
//Works and proves my library list reflects 
//what I passed in when creating the connection.

//$sql = "SELECT * FROM LIBRARY_LIST_INFO";
//Generates: "42S02 : [IBM][CLI Driver][AS] SQL0204N "<user name>.LIBRARY_LIST_INFO" is an undefined name. SQLSTATE=42704 SQLCODE=-204"
//where <user name> is the username used to connect to the DB.
//It should be using the library list specified when creating the connection though.  
//This holds true for any table from any library including those specified 
//when creating the connection (which includes QSYS2).

$stmt = db2_prepare($conn, $sql);
$result = db2_execute($stmt);
if($result){
    while($row = db2_fetch_assoc($stmt)){
        echo "<pre>";
        var_dump($row);  //In addition to entries for QSYS, QSYS2, QUSRSYS and QHLPSYS I get entries for MYLIB, YOURLIB and ANYLIB.
        echo "</pre>";
    }
}else{
    echo "failed<br />";
    echo db2_stmt_error()." : ".db2_stmt_errormsg()."<br />";
}

Has anyone ever run into this while enabling i5_naming when connecting to a remote DB2 server? I'm not really sure why it wouldn't be using my library list as the PHP manual states "Unqualified files are resolved using the library list for the job." when enabled. http://php.net/manual/en/function.db2-connect.php

评论

I finally solved this after opening a PMR with IBM. All I had to do was apply the latest Fix Pack for DB2 Connect Personal Edition.

Suggested Fix Packs for DB2 Connect:

http://www-01.ibm.com/support/docview.wss?rs=71&uid=swg21321001

Basically the DB2 Connect version I had was released prior to 2013. It was in 2013 IBM added two tier support by adding the i5_naming option. So my DB2 Connect setup was effectively ignoring the option I was passing. So that explains why the other options still went through. On the DB side, since it didn't receive a value for i5_naming - it remained as the default.

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • 我收到“操作超时。 ERRORCODE=-4499,SQLSTATE=08001”连接到远程 DB2(I am getting “ Operation timed out. ERRORCODE=-4499, SQLSTATE=08001” connecting to remote DB2)
    问题 我正在尝试使用 IBM Type 4 JDBC 驱动程序连接到远程 DB2。 这是我的配置: 服务器: Windows 7 专业版 DB2 LUW V10.5 DB2 SVCENAME=50000 TCP/IP 通讯 客户: OS/x V10.10.3 日食火星 IBM DB2 Java Type 4 驱动程序 我的理解是,如果您用 Java 编写客户端应用程序并使用类型 4(纯 Java)驱动程序,则客户端不必安装客户端。 该应用程序将使用 DRDA 直接连接到远程数据库。 这是我尝试访问远程 db2 的一段代码: public class BlobRetrieval { static String databaseDriver; static String dbURL; static String userName; static String password; static Connection passConn; public static void main(String[] args) { databaseDriver = "com.ibm.db2.jcc.DB2Driver"; dbURL = "jdbc:db2://192.168.1.81:50000/LOBDB"; userName = "ace"; password = "ace"; try {
  • How do I access AS/400 using SQLAlchemy?
    Short version: Please tell me how to connect to AS/400s via SQLAlchemy. Long version My ultimate goal is to join data from SQL Server and AS/400 to be displayed in a Flask Python application. My approach has been to get the data from each database into Pandas dataframes, which can then be joined and output as JSON. If anyone has a better approach, feel free to leave a comment. The problem with the way I'm trying to do this is that Pandas.read_sql_query() relies on SQLAlchemy, and getting SQLAlchemy to work with AS/400 is proving quite difficult. The AS/400 is version 7.2, though another I will
  • Connect PHP to AS400 with ODBC or DB2
    I'm trying to connect my WEB server to AS400. The web server has not DB2 library neither ODBC library. I have installed XAMPP in my Windows computer, and one of my colleagues also did. He has Client Access on his Computer. We both installed: - XAMPP - ibm_data_server_driver_package_win32_v10.5.exe We tried to install PECL DB2 extension (LINK) but unsuccessfully (got error ".\php.exe appears to have a suffix .exe, but config variable php": seems that nobody has solved this problem on Windows...). Then we saw that XAMPP has the ODBC Module already on it, so we tried to estabilish a connection
  • 使用 ODBC 或 DB2 将 PHP 连接到 AS400(Connect PHP to AS400 with ODBC or DB2)
    问题 我正在尝试将我的 WEB 服务器连接到 AS400。 Web 服务器既没有 DB2 库也没有 ODBC 库。 我已经在我的 Windows 计算机上安装了 XAMPP,我的一位同事也安装了。 他的计算机上有客户端访问权限。 我们都安装了: - XAMPP - ibm_data_server_driver_package_win32_v10.5.exe 我们尝试安装 PECL DB2 扩展 (LINK) 但没有成功(出现错误".\php.exe appears to have a suffix .exe, but config variable php" :似乎没有人在 Windows 上解决过这个问题......)。 然后我们看到 XAMPP 上已经有 ODBC 模块,因此我们尝试与obdc_connect建立连接。 参考这个问题,我们现在可以使用他的计算机和Client Access Drivers连接到 AS400,使用: $user = 'USER'; $password = 'PASS'; $hostname = '192.168.1.30'; $server="Driver={Client Access ODBC Driver (32-bit)}; System=$hostname; Uid=$user; Pwd=$password;"; odbc_connect(
  • User and Password when Connecting to DB2 via JDBC
    I am trying to connect to a local DB2 10.5 Express-C server. This is a testing environment so I don't care about security. I am able to connect to the Command Line Processor (running on Windows), and I changed the configuration settings for AUTHENTICATION=CLIENT and TRUST_ALLCLNTS=YES. I expected to be able to connect without authentication at that point (I restarted the DB2 service), but I still get an error. When I try to connect without username/password jdbc:db2://localhost:50000/test I get the error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][t4][10205][11234][4.14.146] Null userid
  • I am getting “ Operation timed out. ERRORCODE=-4499, SQLSTATE=08001” connecting to remote DB2
    I am attempting to connect to a remote DB2 using IBM Type 4 JDBC driver. Here is my configuration: Server: Windows 7 professional DB2 LUW V10.5 DB2 SVCENAME=50000 TCP/IP to communicate Client: OS/x V10.10.3 Eclipse Mars IBM DB2 Java Type 4 drivers It is my understanding that if you write the client app in Java and use the type 4—pure Java—drivers, the client doesn't have to have a client installed. The app will use DRDA to connect directly to the remote database. Here is a snippet of code that I tried to access the remote db2: public class BlobRetrieval { static String databaseDriver; static
  • OGG兼容性列表
    OGG兼容性列表 OGG的版本对OS和数据库的版本有严格要求,具体查看:https://www.oracle.com/middleware/technologies/fusion-certification.html 11.2.1+版本的OGG:Installation TypeVersion SupportedProcessor TypeOS VersionOS 32/64 bitSupported Database Versions*Exceptions and Additional InformationOGG Core11.2.1.0.19+x64Windows Server 2008 R264 bitMicrosoft SQL Server 2005Microsoft SQL Server: Capture and Delivery for customers licensed before June 1st, 2016. Delivery for all customers. Only Enterprise Edition supported for SQL ServerOGG Core11.2.1.0.19+x64Windows Server 2008 with SP1+64 bitMicrosoft SQL Server 2005Microsoft SQL
  • 连接到IBM AS / 400 DB2数据库(Connecting to an IBM AS/400 DB2 Database)
    问题 我正在尝试使用PHP的ODBC Driver从Ubuntu Server连接到客户端的IBM AS / 400 DB2数据库。 我也安装了unixODBC 。 我的odbcinst.ini看起来像这样: [IBM DB2 ODBC DRIVER] Description = ODBC 5.1 Driver for Database Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so FileUsage = 1 我的odbc.ini看起来像这样: [IBM DB2 ODBC DRIVER] Driver = IBM DB2 ODBC DRIVER Description = ODBC 5.1 Driver DSN 现在,我要连接的代码是: $server = '12.345.678.90' //IP $port = '446' //PORT $username = 'my_username'; $password = 'my_password'; $connect = odbc_connect("DRIVER = {IBM DB2 ODBC DRIVER};System=$server:$port;Uid=$username;Pwd=$password;", $username, $password); if(
  • Connecting to an IBM AS/400 DB2 Database
    I'm trying to connect to a client's IBM AS/400 DB2 Database from an Ubuntu Server using PHP's ODBC Driver. I have the unixODBC installed as well. My odbcinst.ini looks like this: [IBM DB2 ODBC DRIVER] Description = ODBC 5.1 Driver for Database Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so FileUsage = 1 And my odbc.ini looks like this: [IBM DB2 ODBC DRIVER] Driver = IBM DB2 ODBC DRIVER Description = ODBC 5.1 Driver DSN Now, my code to connect is: $server = '12.345.678.90' //IP $port = '446' //PORT $username = 'my_username'; $password = 'my_password'; $connect = odbc_connect("DRIVER =
  • PHP/Linux to AS/400-db2
    I am trying to get php on Linux Centos server access as/400 (iSeries) db2 database. I am using this IBM guide as much as possible, (though we could not get GUI configuration utility working.) http://www-03.ibm.com/systems/i/soft...ide/index.html I downloaded and successfully installed iSeriesAccess drivers and pre-requisites. rpm -i iSeriesAccess-5.4.0-1.6.i386.rpm I have configured these files to define drivers/DNS: /etc/odbc.ini and /etc/odbcinst.ini [iSeries Access ODBC Driver] Description = iSeries Access for Linux ODBC Driver Driver = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so Setup = /opt
  • IBM db2 driver deployment / installation
    I'm trying to connect to IBM DB2 database with C# application. At first I've installed IBM Data Server Driver Package, which, according to the documentation is for applications using ODBC, CLI, .NET, OLE DB, PHP, Ruby, JDBC, or SQLJ, use IBM Data Server Driver Package. Then I thought I could use IBM.Data.DB2 assembly and happily connect. It didn't work at all, as the app kept crashing while looking for db2app.dll in its current working directory. Then I installed IBM Data Server Client, which has this dll in "bin" directory, which I added to system path. That didn't help, so I copied it to my
  • IBM db2 驱动部署/安装(IBM db2 driver deployment / installation)
    问题 我正在尝试使用 C# 应用程序连接到 IBM DB2 数据库。 首先,我安装了IBM Data Server Driver Package ,根据文档,它适用于使用 ODBC、CLI、.NET、OLE DB、PHP、Ruby、JDBC 或 SQLJ 的应用程序,使用 IBM Data Server Driver Package 。 然后我想我可以使用IBM.Data.DB2程序集并愉快地连接。 它根本不起作用,因为该应用程序在其当前工作目录中查找db2app.dll一直崩溃。 然后我安装了IBM Data Server Client ,它在“bin”目录中有这个 dll,我将它添加到系统路径中。 这没有帮助,所以我将它复制到我的应用程序的构建目录,现在它可以工作了。 但我认为这是不正确的,因为我还必须将msg\en_US\db2nmp.xml文件复制到我的构建目录以获取异常错误文本。 我认为从 DB2 安装中复制所有这些文件是错误的,我应该能够以某种方式强制IBM.Data.DB2程序集在系统路径中而不是在其工作目录中查找所需的文件。 我也认为Data Server Driver Package应该足够了,但是在我安装了更大的Data Server Client之前,我无法对IBM.Data.DB2.dll做任何事情。 我认为问题出在我这边,而不是 IBM
  • 安装分析后,我的 Worklight 6.2 控制台上没有运行时(No runtime on my Worklight 6.2 Console after installing analytics)
    问题 我刚刚安装了Worklight 6.2服务器,使用配置工具部署了一个简单的项目,运行正常。 然后我按照知识中心的说明安装分析。 之后,当我在浏览器中打开 WL 控制台时,我得到“找不到运行时”。 分析似乎工作正常 我的 war 文件位于 Liberty 应用程序文件夹中。 在我的情况下 C:\IBM\WebSphere\Liberty\usr\servers\simpleServer\apps 我已经尝试了下面的解决方案,但没有奏效。 我删除了工作区中的文件并检查了我的 jdk,即 jdk6_45 在我的 Worklight 6.2 控制台上没有运行时 这是我的 server.xml <!-- Enable features --> <featureManager> <feature>jsp-2.2</feature> <!-- Begin for analytics --> <feature>jsp-2.2</feature> <feature>jndi-1.0</feature> <!-- End for analytics --> <!-- Begin of features added by IBM Worklight installer. --> <feature>ssl-1.0</feature> <feature>servlet-3.0</feature>
  • No runtime on my Worklight 6.2 Console after installing analytics
    I just installed Worklight 6.2 server, use the configuration tool to deploy a simple project and it works OK. Then I followed the instructions in knowledge center to install analytics. Afterwards, when I open WL console in the browser, I got "No runtime can be found." Analytics seems to work fine My war file is in Liberty apps folder. in my case C:\IBM\WebSphere\Liberty\usr\servers\simpleServer\apps I have tried the solution below, but didn't work. I removed the files in workarea and checked for my jdk, which is jdk6_45 No runtime on my Worklight 6.2 Console Here's my server.xml <!-- Enable
  • 使用Zend Framework连接到两个不同的数据库(connecting to two different databases with Zend Framework)
    问题 我这里有一个中等大小的Intranet网站,完全用Zend FW编写。 Intranet的数据库位于另一台服务器上。 现在,我需要使用一些新功能来扩展Intranet。 为此,我需要连接到同一服务器(和同一DBMS)上的另一个数据库。 现在的问题是:执行此操作的最佳方法是什么? 我应该创建一个新的Zend_Config对象和一个新的Zend_Db_Adapter吗? 还是应该使用现有的,然后尝试使用“ use otherdbname;”? 在同一会话内连接到新数据库的语句? 还是有更好的方法呢? 回答1 一种选择是从bootstrap.php注册2个数据库句柄,每个连接一个。 例如: $parameters = array( 'host' => 'xx.xxx.xxx.xxx', 'username' => 'test', 'password' => 'test', 'dbname' => 'test' ); try { $db = Zend_Db::factory('Pdo_Mysql', $parameters); $db->getConnection(); } catch (Zend_Db_Adapter_Exception $e) { echo $e->getMessage(); die('Could not connect to database.'); }
  • 尝试通过 Inline::Java 模块将 Java 内联到 Perl(Attempting to inline Java into Perl via the Inline::Java module)
    问题 这是我第一次尝试在 Perl 中内联 Java 代码。 我们不能在我们的系统上使用标准的 SFTP 命令。 这超出了我的能力。 我们有一个可以使用的名为 SFTP.jar 的 jar 文件。 我之前的前一个人能够让 Inline::Perl 工作,但他的实现很草率,我想清理它。 我正在H:\svn目录上的 Windows 系统上工作。 我的模块在H:\svn\FMS3目录下,我在H:\svn\FMS3\Sftp.pm目录下有一个名为SFTP.jar的 jarfile。 在H:\svn\FMS3目录下有一个名为Sftp.pm的文件,它定义了一个名为FMS3::Sftp的模块。 (我将所有自定义模块保存在FMS3父模块下)。 我在FMS3::Sftp内联了代码,看起来它可以编译。 但是,当我尝试执行时,它声称Can't exec JVM: open3: Can't call method "close" on an undefined value at C:/Perl/lib/IPC/Open3.pm line 370 。 我到底做错了什么? FMS3::Sftp模块: #! /usr/bin/env perl # Sftp.pm ######################################################################## ##
  • 如何在 IBM System i Access for Windows GUI 工具中调用存储过程(How to call a stored procedure in IBM System i Access for Windows GUI Tool)
    问题 我想测试一个在 AS400 系统上运行的 DB2 存储过程。 我安装了适用于 Windows 的 IBM System i Access,并且可以针对 DB2 数据库运行 SQL 命令。 我的问题是:执行接受参数并将结果作为输出参数返回并将值打印到屏幕的存储过程的语法是什么? 澄清一下:我不是在问如何在代码中调用 proc。 我想执行proc并在gui工具(类似于SQL Enterprise Manager)中查看结果。 回答1 使用关键字call并传入参数。 call myStoredProc(parm1, parm2, ?); 有关更多详细信息,请参见 http://www.ibm.com/developerworks/data/library/techarticle/dm-0503melnyk/。 有趣的部分是图 5. 使用命令编辑器调用 SQL 过程 回答2 你想要的都是可能的。 我自己已经做过很多次了。 不幸的是,我现在不在办公室,所以它一定是从我的头顶上来的。 启动 System i Access 转到您的 iSeries 图标并登录到您的存储过程所在的图标转到数据库图标并连接到正确的图标(您有一个本地的,可能还有一个或多个遥控器) 只有这样,您才会在屏幕底部看到“运行 SQL 脚本”选项启动该选项,您将看到一个 SQL 编辑器(顶部的编辑器,底部的查看器/消息)
  • Attempting to inline Java into Perl via the Inline::Java module
    This is my first attempt to inline Java code in Perl. We cannot use the standard SFTP command on our system. This is out of my power. We have a jarfile called SFTP.jar which can be used. The previous person before me was able to get Inline::Perl to work, but his implementation was sloppy, and I'd like to clean it up. I am working on a Windows system on the H:\svn directory. I have my module under the H:\svn\FMS3 directory, and I have a jarfile called SFTP.jar under the H:\svn\FMS3\Sftp.pm directory. There is a file called Sftp.pm located under the H:\svn\FMS3 directory, and defines a module
  • 如何查看DB2表结构(How to view DB2 Table structure)
    问题 如何在DB2数据库中查看表结构 回答1 我从sysibm.syscolumns得到了答案 Select distinct(name), ColType, Length from Sysibm.syscolumns where tbname = 'employee'; 回答2 通常,使用DESCRIBE最简单。 DESCRIBE TABLE MYSCHEMA.TABLE 或者 DESCRIBE INDEXES FOR MYSCHEMA.TABLE SHOW DETAIL 等等。 请参阅文档:DESCRIBE命令 回答3 在DB2中,在db2命令提示符下输入。 db2 => describe table MyTableName 回答4 另外,以下命令也适用: describe SELECT * FROM table_name; select语句可以用任何其他select语句替换的地方,例如,对于带有select的复杂插入,这非常有用。 回答5 对于IBM DB2 10.7版本中的表描述,我对此进行了尝试并很好地进行了工作。 SELECT NAME,COLTYPE,NULLS,LONGLENGTH FROM SYSIBM.SYSCOLUMNS where TBcreator =SCHEMANAME and TBNAME =TABLENAME; 回答6 如何在db2数据库中查看表结构
  • DB2数据库的备份与恢复
    一、 查看数据库 [db2inst1@localhost ~]$ db2 list db directory 二、 创建测试数据库 $ db2 create database test 三、 启动进入创建的测试库 $ db2 connect to test l 列出激活的库 $ db2 list active databases l 查看表空间 $ db2 list tablespaces l 列出库中的表 $ db2 list tables Table/View Schema Type Creation time ------------------------------- --------------- ----- -------------------------- 0 record(s) selected. l 创建表 $ db2 "create table student(id int,fname varchar(30),age int)" l 赋予用户权限 db2 "grant use of tablespace TABLESPACE_TEST to user XXX" l 向表中插入数据 $ db2 "insert into student values (1,'Tom',22)" l 查询表内容 四、 在线备份 备份指定库 $ db2 backup db TEST