在DB2 / 400 SQL查询中动态引用表名。 [英] Refer to table name dynamically in DB2/400 SQL query..?

问题描述

我想运行一个查询，该查询将实时数据和元数据结合在一起，以获取定期更改的表名列表。这是为了研究和在大型服务器上进行分析，该服务器具有100多个模式，每个模式中都有成千上万个表/视图。我需要动态引用表名的帮助，我知道这是不可能的。但是...

I'd like to run a query that gets a combination of live data and metadata for a regularly changing list of table names. This is for research & analysis on a large server with 100+ schemas and thousands of tables/views in each. I need help referring to table names dynamically, which I do understand is NOT possible. However...

我的谷歌搜索表明解决方案可能是文本变量中的SQL语句，然后由EXEC语句执行。但这是DB2 / 400 v7r3，这很复杂（就像IBM网站上的SQL参考一样），我很难创建正确的语法。

My googling indicates the solution may be an SQL statement in a text variable, which is then executed by the EXEC statement. But this is DB2/400 v7r3, which is a complex thing (as is its SQL reference on the IBM web site), and I'm having difficulty creating correct syntax.

这是我要执行的操作的一个基本示例，但是它当然不起作用：

Here is a basic example of what I wish to do, but of course it does not work:

SELECT        TABLE_NAME,
              TABLE_TEXT,
              ( SELECT COUNT(*) FROM TABLE_NAME ) AS ROW_COUNT
              -- above line of course does not work
FROM          QSYS2.SYSTABLES
WHERE         TABLE_SCHEMA = 'ABCDEFGH'
AND           TABLE_NAME IN ('ARCMNTST', 'ARIMSGT', 'ARTENT', 'DAILYHT', 'ETC')

我了解我需要以下内容，但我无法找出正确的语句：

I understand I need something like the following, but I cannot figure out the correct statements:

DECLARE       @sqltext AS VARCHAR(128)
SELECT        TABLE_NAME,
              TABLE_TEXT,
              ( SET @sqltext = 'SELECT COUNT(*) FROM ABCDEFGH.' || TABLE_NAME
                EXEC sqltest ) AS ROW_COUNT --this is probably wrong
FROM          QSYS2.SYSTABLES
WHERE         TABLE_SCHEMA = 'ABCDEFGH'
AND           TABLE_NAME IN ('ARCMNTST', 'ARIMSGT', 'ARTENT', 'ETC', 'ETC', 'ETC')
ORDER BY      TABLE_NAME

推荐答案

动态SQL并不困难...

Dynamic SQL isn't difficult...

基本上，将SQL语句构建到字符串变量中。使用 CONCAT 包含其他值

Basically, build your SQL statement into your string variable. Using CONCAT to include values from other

@sqlStmt = 'Insert into mytable values (''ConstVal'',' concat SomeVar concat ')';

execute immediate @sqlStmt;

要注意的是，您必须转义字符串，例如上面的'ConstVal'，双引号。

The catch is that you have to escape strings, like 'ConstVal' above with double single quotes.

另一个问题是，您不能像尝试使用的那样使用 SELECT 。如果只有一行要返回，则 SELECT INTO 将是静态语句的一个选项。但是动态不支持。您必须使用动态 VALUES INTO 。

The other catch is that you can't use SELECT like you are trying to. If you only had one row to return, SELECT INTO would be an option for a static statement. But isn't supported for dynamic. You have to use a dynamic VALUES INTO instead.

但是，您似乎想要返回多行。在这种情况下，您需要使用游标。不幸的是，动态游标要复杂一些，因为您必须使用SQL描述符。

However, it appears you want to get back multiple rows. In which case, you need to use a cursor. Unfortunately, dynamic cursors are a bit more complex as you have to use the SQL Descriptor.

declare myCursor cursor for myStatement;

set @sqlStmt = 'select ....';
prepare myStatement into mySqlDescriptor from @SqlStmt;

open myCursor;
// done if you are returning the results
// assuming you want to process in your procedure..
// add a loop that does 
//   fetch next from myCursor into myData;

说了这么多，您不需要任何它来获取表的行数... syspartitionstat目录视图已经具有该信息。

Having said all that, you don't need any of it to get a row count for a table...the syspartitionstat catalog view already has that info.

select table_name, number_rows 
from syspartitionstat
where table_schema = 'ABCDEFGH' 
      and TABLE_NAME in ('ARCMNTST', 'ARIMSGT', 'ARTENT', 'ETC', 'ETC', 'ETC');

这篇关于在DB2 / 400 SQL查询中动态引用表名。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！