AS400/DB2跨库查询问题
我正在我的应用程序中从 ODBC 查询 Iseries,并尝试执行从 2 个表返回结果的查询。 我需要加入这些表,但这些表位于不同的库中。 我不想在查询中使用库标识符,因为当我从 dev>qa>prod 移动时,我的库会发生变化。 但是,我确信这些表只会在这些库中,而在其他库中不会有重复的表。 有没有办法在不指定 Iseries 库的情况下执行此操作?
从本质上讲,我想要这样:
select DISTINCT GIDNBR, VSL00100.GRNAME
FROM **QACOMMON**.CMPGRID2 CMPGRID2 INNER JOIN **QAVISLIVE**.VSL00100 VSL00100 ON VSL00100.GRNO=CMPGRID2.GIDNBR AND
VSL00100.GRSUB=CMPGRID2.GIDSUB AND
VSL00100.GRLOC=CMPGRID2.GIDLOC AND
VSL00100.GRPOOL=CMPGRID2.GIDPOL
看起来像这样:
select DISTINCT GIDNBR, VSL00100.GRNAME
FROM CMPGRID2 CMPGRID2 INNER JOIN VSL00100 VSL00100
ON
VSL00100.GRNO=CMPGRID2.GIDNBR AND
VSL00100.GRSUB=CMPGRID2.GIDSUB AND
VSL00100.GRLOC=CMPGRID2.GIDLOC AND
VSL00100.GRPOOL=CMPGRID2.GIDPOL
有什么想法吗?
I'm querying an Iseries from ODBC in my app and am trying to perform a query that returns results from 2 tables. I need to join the tables but the tables are in different libraries. I don't want to use library identifiers in my query as my libraries change as I move from dev>qa>prod. However, I am certain that these tables will only be in these libraries and there will be no duplicates in other libraries. Is there a way to do this without specifying the Iseries library?
In essense, I want this:
select DISTINCT GIDNBR, VSL00100.GRNAME
FROM **QACOMMON**.CMPGRID2 CMPGRID2 INNER JOIN **QAVISLIVE**.VSL00100 VSL00100 ON VSL00100.GRNO=CMPGRID2.GIDNBR AND
VSL00100.GRSUB=CMPGRID2.GIDSUB AND
VSL00100.GRLOC=CMPGRID2.GIDLOC AND
VSL00100.GRPOOL=CMPGRID2.GIDPOL
to look like this:
select DISTINCT GIDNBR, VSL00100.GRNAME
FROM CMPGRID2 CMPGRID2 INNER JOIN VSL00100 VSL00100
ON
VSL00100.GRNO=CMPGRID2.GIDNBR AND
VSL00100.GRSUB=CMPGRID2.GIDSUB AND
VSL00100.GRLOC=CMPGRID2.GIDLOC AND
VSL00100.GRPOOL=CMPGRID2.GIDPOL
Any ideas?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您需要进入 ODBC 连接的配置。 在您使用的连接上单击“配置”,然后选择“服务器”选项卡。 库列表选项是您需要使用的。
根据您的示例,您似乎需要将其写为“QACOMMON QAVISLIVE”。 (不带引号)
此外,您还需要将命名约定更改为 *SYS 而不是 *SQL,并将默认集合字段设为空白。 确保您的系统管理员将您正在使用的用户配置文件上的 CURLIB 参数设置为您希望新文件存放的位置。
然后保存并重试。 不幸的是,当您在测试、生产和其他任何内容之间切换时,您必须拥有不同的数据源或更改 ODBC 连接。
You need to go into the configuration for your ODBC connection. On the connection you are using click on "Configure", then select the "Server" tab. The library list option is what you'll need to play with.
Based on your examples it looks like you need to have it say "QACOMMON QAVISLIVE". (with no quotes)
Also you will need to change the naming convention to *SYS instead of *SQL and make the default collection field blank. Make sure your system administrator sets the CURLIB parameter on the user profile you are using to where ever you want new files to go.
Then just save it and try it again. Unfortunately you'll have to either have different data sources or change the ODBC connection when you are switching around between test, production, and whatever else you have.