在联合查询中查找表总数 [英] Finding total number of tables in union query

问题描述

我正在编写支持不同版本的 Sybase ASE 的代码.我正在使用联合查询，问题是不同版本的 Sybase ASE 在联合查询中支持不同数量的表.联合查询是动态的，将根据服务器中存在的数据库数量形成.

I am writing a code which supports different versions of Sybase ASE. I am using union queries and the problem is that different version of Sybase ASE supports different number of tables in union query. The union query is dynamic and will be formed depending on the number of database present in the server.

有什么方法可以找到特定 Sybase ASE 支持的最大表数?我现在知道的唯一解决方案是使用查询获取版本并从结果中挑选出版本号并在代码中相应地设置数字.但这不是一个很好的解决方案.我尝试检查是否有任何表存储了这个值，但没有出现.任何人都可以为此提出任何解决方案吗?

Is there any way in which I can find the max number of tables supported by a particular Sybase ASE? The only solution that I know right now is to fetch the version using query and pick out the version number from the result and set the number accordingly in the code. But this is not a very good solution. I tried checking if there are any tables which have stores this value but nothing came up. Can anyone suggest any solution for this?

推荐答案

由于这是我的 SAP 回复，您已在此处重新发布，我将添加更多注释...

Since that's my SAP response you've re-posted here, I'll add some more notes ...

• 这是一个概念证明，它回答了如何通过 T-SQL 获取信息的基本问题；假设任何真正希望实施该解决方案的人(最终)都会解决各种问题:开销/维护，例如......

• that was a proof of concept that answered the basic question of how to get the info via T-SQL; it was assumed anyone actually looking to implement the solution would (eventually) get around to addressing the various issues re: overhead/maintenance, eg ...

设置跟踪文件需要权限才能做到；哪些权限取决于您是否启用了细粒度权限(请参阅参考手册中set tracefile"命令的注释)；您需要决定是否/如何向其他用户授予权限

setting a tracefile is going to require permissions to do it; which permissions depends on whether or not you've got granular permissions enabled (see the notes for the 'set tracefile' command in the Reference manual); you'll need to decide if/how you want to grant the permissions to other users

虽然您不能重复使用跟踪文件，但您可以为跟踪文件所在的目录创建一个代理表，然后从目录中删除"跟踪文件，例如:

while it's true you cannot re-use the tracefile, you can create a proxy table for the directory where the tracefile exists, then 'delete' the tracefile from the directory, eg:

在 '/tmp' 创建 proxy_table tracedir 外部目录去删除 tracedir where filename = 'my_serverlmiits'去

• 如果您可以同时运行多个代理表解决方案副本，那么您显然 (?) 需要确保为每个会话生成唯一的跟踪文件名称；虽然您可以通过将@@spid 附加到文件名来完成此操作，但您还可以添加登录名 (suser_name())、kpid(从 master..monProcess 中选择 KPID，其中 SPID = @@spid)等；在尝试创建它之前，您还需要确保这样的文件不存在(例如，删除 tracedir where filename = '.....'; set tracefile ...)

• if you could have multiple copies of the proxy table solution running at the same time then you'll obviously (?) need to make sure you generate a unique tracefile name for each session; while you could do this by appending @@spid to the file name, you could also add the login name (suser_name()), the kpid (select KPID from master..monProcess where SPID = @@spid), etc; you'll also want to make sure such a file doesn't exist before trying to create it (eg, delete tracedir where filename = '.....'; set tracefile ...)

您的错误(从代理表中选择时)似乎与您在事务隔离级别 0 中运行的客户端应用程序有关(默认情况下，它需要表上的唯一索引......不是你的东西)将针对指向操作系统文件的代理表完成)；尝试将隔离级别设置为 1，或使用不默认为隔离级别 0 的客户端应用程序(例如，该示例使用基本的 isql 命令行工具运行良好)

your error (when selecting from the proxy table) appears to be related to your client application running in transaction isolation level 0 (which, by default, requires a unique index on the table ... not something you're going to accomplish against a proxy table pointing to an OS file); try setting your isolation level to 1, or use a client application that doesn't default to isolation level 0 (eg, that example runs fine with the basic isql command line tool)

如果要将此解决方案生产化，那么您可能希望分配一个单独的文件系统，以便任何逃跑"的跟踪会话不会填满重要的文件系统(例如/var、/tmp、$SYBASE 等)

if this solution were to be productionalized then you'll probably want to get a separate filesystem allocated so that any 'run away' tracing sessions don't fill up an important filesystem (eg, /var, /tmp, $SYBASE, etc)

同样从生产/安全的角度来看，我可能想调查在 DBA/系统过程中封装大量细节的可能性(创建为在创建者的权限下执行)以便确保开发人员不能在错误的"目录中创建跟踪文件……等等……控制/安全……

also from a production/security perspective, I'd probably want to investigate the possibility of encapsulating a lot of the details in a DBA/system proc (created to execute under the permissions of the creator) so as to ensure developers can't create tracefiles in the 'wrong' directories ... and on and on and on re: control/security ...

然后……

如果您要LOT 这样做......并且您只对(联合)查询中的最大表数感兴趣，那么它可能会很多更容易构建一个静态的 if/then/else(或 case)表达式，该表达式与您的 ASE 版本与少数可能的数字相匹配(请参阅 RobV 的帖子).

If you're going to be doing this a LOT ... and you're only interested in the max number of tables in a (union) query, then it'd probably be much easier to just build a static if/then/else (or case) expression that matches your ASE version with the few possible numbers (see RobV's post).

让我们面对现实，真正、真的、真的要构建一个包含超过 100 个表的查询的频率有多高，更不用说 500、1000 个了?【你真的不想对付试图调教这样的怪物！！YIKES] 实际上，当您可以实现硬限制(例如，最多 100 个表).

Let's face it, how often are really, Really, REALLY going to be building a query with more than, say, 100 tables, let alone 500, 1000, more? [You really don't want to deal with trying to tune such a monster!! YIKES] Realistically speaking, I can't see any reason why you'd want to productionalize the proxy table solution just to access a single row from dbcc serverlimits when you could just implement a hard limit (eg, max of 100 tables).

我想得越多，作为一名 DBA，我将尽我所能确保您的应用程序不会创建一些庞大的、数百个表的查询，而这些查询最终会导致我的数据服务器陷入困境，仅仅因为开发人员无法提出更有效的解决方案.[天堂禁止这种类型的应用程序被推广到一般用户社区，即，我必须处理在我的数据服务器中运行的这个怪物的数十/数百个副本?！?！?！]

And the more I think about it, as a DBA I'm going to do whatever I can to make sure your application can't create some monster, multi-hundred table query that ends up bogging down my dataserver simply because the developer couldn't come up with a more efficient solution. [And heaven forbid this type of application gets rolled out to the general user community, ie, I'd have to deal with dozens/hundreds of copies of this monster running in my dataserver?!?!?!]

这篇关于在联合查询中查找表总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！