※Microsoft Excel通过ODBC访问AS/400灵活、实时提取DB2.

在企业的日常工作中，出于数据安全和ERP系统功能的有限性，常需要从ERP系统后台DB2借助CA/400的Transfer下载数据到PC端，并转换成EXCEL后再利用手工或VBA语言等进行处理，然后再提供给相关需求部门。
    使用Transfer功能，不但需要安装CA/400或者PCOM的5250仿真软件，而且在使用时需要选择、添加一系列的枯燥查询条件，能否直接集成在EXCEL中，并实现实时访问、灵活处理呢？答案是肯定的。

[b:6d435d57ed][环境][/b:6d435d57ed]

OS/400 V5R2M0 + WinXP  Professional + Microsoft Excel 2003
本文以LIU/EMPLOYEES的数据库为例

[b:6d435d57ed][步骤][/b:6d435d57ed]

[b:6d435d57ed]一、创建ODBC数据源：[/b:6d435d57ed]

打开：控制面板—性能和维护—管理工具—数据源(ODBC)—系统DSN，添加“Client Aclearcase/" target="_blank" >ccess ODBC Driver (32-bit)”驱动程序。
在“常规”栏中添加“数据源名：ExcelDB2”。
在“服务器”栏中指定“SQL缺省库：LIU”，出于数据安全，“连接类型”指定为“只读(只允许Select语句)”。
为避免出现转换数据时的不必要麻烦，建议在“转换”栏把“将二进制数据(CCSID 65535)转换为文本”选项选上。

※备注：必须安装CA/400中ODBC驱动组件才能有“Client Access ODBC Driver (32-bit)”驱动程序。

qingzhou 回复于：2005-03-13 14:01:10 [b:86c3efee6b]二、运用Excel“导入外部数据”功能提取DB2数据：[/b:86c3efee6b] 1、打开Excel2003，数据—导入外部数据—新建数据库查询：   2、选择已经创建好的“ExcelDB2”数据源，确定。  qingzhou 回复于：2005-03-13 14:02:57 3、由于创建ODBC时指定SQL默认的LIB为LIU，所以Excel检索DB2时能够快速检索LIU库下的数据库。 选择“EMPLOYEES”，下一步：   4、出现如下“筛选数据”窗口，可进行选择查询条件。下一步：  qingzhou 回复于：2005-03-13 14:07:23 5、出现“排序顺序”，根据实际的业务需求来进行操作，下一步：   6、将数据返回到Microsoft office excel，完成。  qingzhou 回复于：2005-03-13 14:08:56 7、提取EMPLOYEES的数据如下，通过查询LIU/EMPLOYEES结果证明与OS/400上的数据是一致的。  qingzhou 回复于：2005-03-13 14:10:57 [b:4591df228f]三、灵活运用Execl“外部数据”各项功能键：[/b:4591df228f] 1、“编辑查询”实现灵活多样的查询功能 a、点击”编辑查询”，然后在查询向导进行筛选数据。  b、比如：指定“EYCODE等于0001”的筛选条件：  qingzhou 回复于：2005-03-13 14:12:43 c、将数据返回到Microsoft office excel结果如下：   2、通过Microsoft Query实施灵活多样的查询DB2功能： a、点击”编辑查询”，在查询向导—选择列窗口“取消”，转换到Microsoft Query编辑查询：  qingzhou 回复于：2005-03-13 14:14:38 b、可采用灵活的SQL语句进行查询定义，如：更换EMPLOYEES.EYCODE=’TRF134’，确定。   c、将数据返回到Excel后，会发现查询结果已经改变过来了。  qingzhou 回复于：2005-03-13 14:16:49 3、当DB2变化时、EXCL通过“刷新数据”实现实时提取最新数据： a 、例如通过DFU追加EMPLOYEES数据库2条新的记录：   b、通过STRQRY查询追加后的结果：  qingzhou 回复于：2005-03-13 14:18:42 c、打开EMPLOYEES.xls表格，点“刷新数据”，EXCEL自动检索DB2并将数据变成最新数据。  ibmas400 回复于：2005-03-13 20:38:21 这么实用的帖子居然没人顶。。。 :em06:  我顶。。顶。。顶。。。。 :mrgreen:  qingzhou辛苦了。  pl421 回复于：2005-03-13 21:26:23 顶,收藏.  michael9406 回复于：2005-03-14 08:20:52 哪天俺一个VBA的，  mario663 回复于：2005-03-14 08:35:08 很好!顶!  just a kid 回复于：2005-03-14 09:50:40 实用又养眼，顶上去，感谢QINGZHOU  亿城雪 回复于：2005-03-14 10:11:32 好贴，step by step。很容易看懂。那天有空试一下。  胖老头 回复于：2005-03-14 12:41:27 挺好，坚决支持。  aiyoo 回复于：2005-03-14 16:23:28 我好命苦啊. 连接主机输入用户密码后出错. 楼主,这个是不是主机权限控制了??  stiffmiao 回复于：2005-03-14 17:04:07 顶。。顶。。。。  polkmn 回复于：2005-03-17 12:40:06 Very Good  longdas 回复于：2005-03-17 14:29:46 真是好。。。。 我顶了。  crazymonkey 回复于：2005-03-18 13:38:12 在solaris下是不是也差不多啊  60133056 回复于：2005-03-19 18:43:51 感谢 并狂顶  ibmas400 回复于：2005-03-20 08:56:02 [quote:8e4211e6bd="crazymonkey"]在solaris下是不是也差不多啊[/quote:8e4211e6bd] solaris是SUN的UNIX专用操作系统，你本地环境在SUN后台安装了什么关系型数据库？Oracle、Informix还是Sybase？ 通过其数据库驱动在PC端创建对应的MS ODBC连接、然后再启动EXCEL，我想原理是一样的，都可以访问。 你身边有solaris这个环境、可以自行测试一下，然后再说说测试结果。 8)  dreamflight 回复于：2005-03-20 11:13:49 顶一下，礼拜一回公司试试看  胖有型 回复于：2005-03-24 10:33:15 实在是太。。。。。好用了。 感谢版主！  fairyboy 回复于：2005-03-24 13:40:56 试过了，我刚好需要这方面的资料，谢谢楼主了，有个问题可以问一下吗？ 如果在EXCEL 中对数据进行修改，那么 在AS400的DB2中的数据也会随之修改吗？  qingzhou 回复于：2005-03-24 14:14:12 [quote:009832be7d="fairyboy"]试过了，我刚好需要这方面的资料，谢谢楼主了，有个问题可以问一下吗？ 如果在EXCEL 中对数据进行修改，那么 在AS400的DB2中的数据也会随之修改吗？[/quote:009832be7d] Microsoft Excel →数据→导入外部数据→新建数据库[color=red:009832be7d]查询[/color:009832be7d]、所以这种方法只适合从DB2/400中筛选出适合条件的数据到Excel表中。 如果想通过Microsoft Excel →ODBC→DB2/400实现[color=red:009832be7d]更新[/color:009832be7d]数据，需要使用VBA+ADO方式才能满足要求。  linyueagle 回复于：2005-03-24 16:14:24 顶 感谢！！！  blogliou 回复于：2005-03-25 11:09:59 不错，有用，顶！  pollux66 回复于：2005-08-24 10:57:12 太好了， 太好了... 收藏起来！！！ ^_^  clampox 回复于：2005-08-24 11:46:25 太好了！不过请问如何把数据导入ORACLE而不是EXCEL？  ibmas400 回复于：2005-08-24 11:53:35 [quote:3c27102115="clampox"]太好了！不过请问如何把数据导入ORACLE而不是EXCEL？[/quote:3c27102115] 下面的链接是DB2/400--->DB2 FOR NT，估计跟到ORACLE相差不远吧。 http://bbs.chinaunix.net/forum/viewtopic.php?t=428506  qingzhou 回复于：2005-08-24 13:10:25 [quote:380e00b9e7="clampox"]太好了！不过请问如何把数据导入ORACLE而不是EXCEL？[/quote:380e00b9e7]  :oops: 这个我也没测试过，关于ORACLE导入数据的方法，具体可以参考如下链接： http://www.dbanotes.net/Oracle/All_About_Oracle_Data_Loading.htm  a01 回复于：2005-08-25 11:06:41 请问如何在400上控制用户通过EXCEL访问400的权限?  qingzhou 回复于：2005-08-25 11:56:24 [quote:d4c50cacd4="a01"]请问如何在400上控制用户通过EXCEL访问400的权限?[/quote:d4c50cacd4] 问得很好！ :m01:  由于诸如：DB2/400→ORACLE 9i、DB2/400→SQL2000、DB2/400→ACCESS、DB2/400→EXCEL、DB2/400→NOTES都有一个共同特点，就是通过ODBC来实现访问，所以只要控制住ODBC也就自然控制了对DB2/400数据库的访问权限。 如何控制ODBC的使用权限呢？ 如果要监控以及控制ODBC的数据传输，可以通过Exit Program来监控Exit point: QIBM_QTF_TRANSFER（程序1） QIBM_QZDA_NDB1(程序2) 程序1： [code:1:d4c50cacd4] PGM        PARM(&RC &STRU)                                DCL      VAR(&RC) TYPE(*CHAR) LEN(1)                     DCL      VAR(&STRU) TYPE(*CHAR) LEN(80)              DCL  VAR(&USER)  TYPE(*CHAR) LEN(10) /* user profile*/  DCL  VAR(&APP1)  TYPE(*CHAR) LEN(10) /* function    */  DCL  VAR(&APP2)  TYPE(*CHAR) LEN(10) /* sub function*/  DCL  VAR(&TFOBJ) TYPE(*CHAR) LEN(10) /* file  name  */  DCL  VAR(&TFLIB) TYPE(*CHAR) LEN(10) /*library      */  DCL  VAR(&TFMBR) TYPE(*CHAR) LEN(10) /* member      */  DCL  VAR(&TFFMT) TYPE(*CHAR) LEN(10) /* format      */  DCL  VAR(&TYPE)  TYPE(*CHAR) LEN(2)  /* journaltype */  MONMSG     MSGID(CPF0000) EXEC(GOTO CMDLBL(EXIT))       CHGVAR     &RC    VALUE('1') /* set return code to +             allow request unless rejected by program */    CHGVAR     &USER  VALUE(%SST(&STRU 1 10))  /*user */    CHGVAR     &APP2  VALUE(%SST(&STRU 21 10)) /*funct*/    CHGVAR     &TFOBJ VALUE(%SST(&STRU 31 10)) /*file */    CHGVAR     &TFLIB VALUE(%SST(&STRU 41 10)) /*libr */    CHGVAR     &TFMBR VALUE(%SST(&STRU 51 10)) /*mbr  */    CHGVAR     &TFFMT VALUE(%SST(&STRU 61 10)) /*fmt  */    LOG:  CHGVAR     VAR(&TYPE) VALUE('X' *CAT &RC)           SNDJRNE  JRN(QAUDJRN) TYPE(&TYPE) ENTDTA(&STRU)       EXIT:ENDPGM  [/code:1:d4c50cacd4]    程序2： [code:1:d4c50cacd4] PGM        PARM(&RC &REQUEST)                                      DCL        VAR(&RC) TYPE(*CHAR) LEN(1) VALUE('1')                DCL        VAR(&REQUEST) TYPE(*CHAR) LEN(700)                    DCL        VAR(&TYPE) TYPE(*CHAR) LEN(2)                         DCL &X1800 *CHAR 4 VALUE(X'00001800') /*create database file*/   DCL &X1801 *CHAR 4 VALUE(X'00001801') /*create source   file*/   DCL &X1802 *CHAR 4 VALUE(X'00001802') /*add    member       */   DCL &X1803 *CHAR 4 VALUE(X'00001803') /*clear  member       */   DCL &X1804 *CHAR 4 VALUE(X'00001804') /*delete member       */   DCL &X1805 *CHAR 4 VALUE(X'00001805') /*file override       */   DCL &X1806 *CHAR 4 VALUE(X'00001806') /*delete file override*/   DCL &X1807 *CHAR 4 VALUE(X'00001807') /*create save file    */   DCL &X1808 *CHAR 4 VALUE(X'00001808') /*clear  save file    */   DCL &X1809 *CHAR 4 VALUE(X'00001809') /*delete file         */   /* OPTIMIZED DATABASE SERVER DECLARES */                        DCL &DBFMT *CHAR  8  /* format name            */                  DCL &DBFID *CHAR  4  /* function identifier    */                /* FOLLOWING PARAMETERS ADDITIONAL FOR FORMAT ZDAD0100 */        DCL &DBFILE *CHAR 128  /* file name              */              DCL &DBLIB  *CHAR  10  /* library name           */              DCL &DBMBR  *CHAR  10  /* member name            */              DCL &DBAUT  *CHAR  10  /* authority to file      */              DCL &DBBFIL *CHAR 128  /* based on file name     */            DCL &DBBLIB *CHAR  10  /* based on library name  */     DCL &DBOFIL *CHAR  10  /* override file name     */     DCL &DBOLIB *CHAR  10  /* override library name  */     DCL &DBOMBR *CHAR  10  /* override member name   */     MONMSG     MSGID(CPF0000) EXEC(GOTO CMDLBL(EXIT))      /* allow request unless rejected by program */            CHGVAR     VAR(&RC) VALUE('1')                          /* set variables from request description */            CHGVAR VAR(&DBFMT)  VALUE(%SST(&REQUEST 21 8))          CHGVAR VAR(&DBLIB)  VALUE(%SST(&REQUEST 161 10))          CHGVAR VAR(&DBMBR)  VALUE(%SST(&REQUEST 171 10))        CHGVAR VAR(&DBOFIL) VALUE(%SST(&REQUEST 329 10))        CHGVAR VAR(&DBOLIB) VALUE(%SST(&REQUEST 339 10))        CHGVAR VAR(&DBOMBR) VALUE(%SST(&REQUEST 349 10))      LOG:                                                      CHGVAR   VAR(&TYPE) VALUE('Z' *CAT &RC)                 SNDJRNE  JRN(QAUDJRN) TYPE(&TYPE) ENTDTA(&REQUEST)    EXIT: ENDPGM          [/code:1:d4c50cacd4] 实施方法： 1、将上述2个程序编译，Owner改为QSECOFR，并使用继承权限； 2、CHGNETA PCSACC(*REGFAC)； 3、使用ADDEXITPGM命令把程序添加到相应的Exit point； 4、可以给这2个程序设置一个权限列表，凡是在权限列表内有权限执行此程序的人才可以利用ODBC下传数据； 5、用DSPJRN JRN(QAUDJRN) ENTTYP(Z1) 就可以查看下传的数据情况了； 6、可以用WRKREGINF查看Exit program设置情况。 注：需要有QAUDJRN，如没有需先WRKSYSVAL修改系统值QAUDCTL、QAUDLVL，然后CRTJRN创建QAUDJRN，创建QAUDJRN。  clampox 回复于：2005-08-25 12:44:07 谢谢楼主指点！ 再问下，如何配置ODBC FOR DB2/400，我的WIN2Kp机器上是DB2 UDB，而服务器是DB2/400，不知道为什么我的ODBC连不上DB2/400。 可能问的问题很弱，但是以前从来没摸过DB2和AS/400，不好意思啊  qingzhou 回复于：2005-08-25 13:09:14 [quote:6a72ad350e="clampox"]谢谢楼主指点！ 再问下，如何配置ODBC FOR DB2/400，我的WIN2Kp机器上是DB2 UDB，而服务器是DB2/400，不知道为什么我的ODBC连不上DB2/400。 可能问的问题很弱，但是以前从来没摸过DB2和AS/400，不好意思啊[/quote:6a72ad350e] 安装CA/400时选择定制安装，将iSeries Access ODBC Driver选择后安装就可以在数据源中包含ODBC for DB2/400驱动程序了。 Win2k/WinXP下：开始—>设置—>控制面板—>管理工具—>数据源(ODBC)，然后在“系统DNS”项—>“添加”—>选择“Client Access ODBC Driver(32-bit)”驱动，然后在“常规”栏给数据源命名、“服务器”栏指定SQL缺省库为你想操作的LIB、同时在“转换”栏将“将二进制数据（CCSID 65535）转换为文本”选项打上勾，确定保存退出，这样就成功创建了ODBC for DB2/400的数据源。 关于如何实现DB2/400—>DB2 UDB，以前我在回下帖时已经图文并茂的方式进行了详细说明，按照步骤可以实现成功访问。 《如何实现对 as400 db2 的追加 移植》 http://bbs.chinaunix.net/forum/viewtopic.php?t=428506 原文转自：http://www.ltesting.net 相关文章 漫画赏析：Linux 内核到底长啥样 Linux的进程优先级 Windows原生运行Linux的技术细节 Linux常用性能调优工具索引 top使用技巧 bash遍历目录 周排行 月排行 下载 全网最详细的接口测试实战 先测试再开发？TDD测试驱动 自动化测试架构 软件测试架构师的知识能力 大数据平台测试方法 用不同的测试模型来构建测 当软件测试遇上ChatGPT：软件 全网最详细的接口测试实战 先测试再开发？TDD测试驱动 自动化测试架构 软件测试架构师的知识能力 大数据平台测试方法 用不同的测试模型来构建测 当软件测试遇上ChatGPT：软件 MBT基于模型的测试介绍资料 iso29119相关介绍性资料 HP QTP 10 中文版官方中文补丁 HP QTP 10 英文版 下载地址 HP ALM 11 官方 中文版下载地址 Quality Center 9.0中文版 下载地 HttpWatch Basic Edition Version 7. WIN2003+ORACLE11G+QC11(ALM11) 安装 WIN2003+SQL2005(SP3)+QC11(ALM11) 安 软件测试沙龙 More>> 新浪微博More>> 热门标签 功能测试 性能测试 安全测试 本地化测试 游戏测试 web测试 单元测试 敏捷测试 测试用例 测试模版 测试管理 测试工具 关于领测软件测试网 | 诚聘英才 | 广告服务 | 投稿指南 | 联系我们 | 网站地图| 友情链接 版权所有(C) 2003－2021 Ltesting(领测软件测试网)|领测国际科技（北京）有限公司 |软件测试培训网 All Rights Reserved 京ICP备2023014753号-2 | 京公网安备11010602004416号-1 技术支持和业务联系：info@ltesting.com.cn 电话：010-51297073