Provide a way to query Db2 for IBM i members

Strongly in favor, with a few technical arguments and one extension.

The strongest point in favor is that this does not introduce a new concept into Db2 for i. It exposes in DML something the platform already models. QSYS2.SYSPARTITIONSTAT and SYSPARTITIONINDEXSTAT already describe members as partitions, with per-member row counts and statistics. Access paths and statistics are already maintained per member. CREATE ALIAS already has the LIB.FILE(MBR) notation. The only place a member cannot be named today is the table reference itself. That is an asymmetry in the SQL surface, not a missing capability in the database.

The two current workarounds are structurally inadequate rather than merely inconvenient.

OVRDBF is job state, not part of the statement. Its scope crosses call levels, so the same SQL statement behaves differently depending on who called the procedure — particularly with service programs activated in *CALLER. It is fragile under multithreading and SQL Server Mode. It is invisible to static analysis, since DSPPGMREF and impact-analysis tooling see the statement but not an override issued three call levels away, which means the real object dependency cannot be reconstructed from source. And it cannot be issued from ODBC, JDBC, the Db2 REST API, IWS, Python or Node.js. In practice, member-partitioned data is unreachable from every modern SQL interface unless wrapped in a native program that calls QCMDEXC. That is a direct obstacle to modernizing historical data that was partitioned by member long before SQL was the primary interface to it.

CREATE ALIAS puts DDL inside application flow. It creates objects, updates the catalog, requires create authority in a library, and takes locks. A CREATE OR REPLACE ALIAS inside a loop over members invalidates plans and forces full opens, defeating exactly the reusable ODP behavior the runtime works to provide. When the member name is user-supplied, it also forces the name to be concatenated into QCMDEXC or dynamic DDL, adding an injection surface and a prepare cost just to select a partition. Under connection pooling, the lifecycle of temporary aliases in QTEMP becomes an additional hygiene problem, including on error paths.

There is also an optimizer argument. Today two executions against different members carry identical statement text, so the Plan Cache cannot distinguish them. Naming the member in the statement gives each member its own cache entry, its own runtime statistics, and meaningful output from Visual Explain and SQL Plan Cache Services. It also delivers the member selection at optimization time rather than as runtime state resolved outside the statement. Since multi-member access via override has historically been CQE territory, native support would also be an opportunity to handle these queries under SQE.

On syntax: TABLE(MEMBER) reads and parses like a UDTF invocation, and would be ambiguous where a table function of the same qualified name exists. A keyword form would be less ambiguous for the parser and consistent with the existing FOR SYSTEM_TIME AS OF extension to the table reference:

sql

SELECT * FROM QIWS.QCUSTCDT FOR MEMBER JUNE AS C;
SELECT * FROM QIWS.QCUSTCDT FOR MEMBER :mbrName AS C;

The second form — member as a host variable or parameter marker — is the highest-value part of the request, since it covers exactly the dynamic case that forces string-built SQL today. It should also be relatively inexpensive: all members of a physical file share one record format, so column list, types and lengths are invariant. The plan does not need invalidation when the member changes; only statistics and access path selection do.

Suggested extension: support *FIRST, *LAST and *ALL alongside an explicit name.

*FIRST costs nothing, since it is already the default, but it makes intent explicit in the source instead of implicit.

*LAST is genuinely new — OVRDBF accepts *FIRST, *ALL or a name, but has no *LAST. It is also the most common real need, because member-partitioned files are almost always appended chronologically and the most recent period is the most frequently queried. To avoid ambiguity it should be defined as the last member in member list order — the same order MBR(*ALL) processes and DSPFD reports — not alphabetical order and not last-changed date. If a date-based semantic is also wanted, it should be a separate keyword rather than an overload of *LAST.

*ALL is the most valuable of the three, but on its own it inherits an existing limitation: MBR(*ALL) returns the union of members with no way to know which member a row came from. It would be far more useful paired with the member name exposed as a pseudo-column or scalar, in the spirit of the existing RRN():

sql

SELECT MEMBER_NAME(C) AS PERIOD, C.*
  FROM QIWS.QCUSTCDT FOR MEMBER *ALL AS C
 WHERE MEMBER_NAME(C) BETWEEN 'JAN' AND 'JUN';

With that, *ALL stops being an opaque UNION ALL and behaves as a genuinely partitioned table, since a predicate on the member name enables partition pruning at optimization time. For DML, *FIRST, *LAST and explicit names should be valid everywhere, while *ALL should be valid for SELECT and arguably UPDATE and DELETE, but not for INSERT or as a MERGE target, where the destination would be undetermined.

On implementation cost, several things reduce the scope. No new security model is needed, since IBM i authority is held at file level rather than member level. There is no impact on journaling or commitment control, both of which already operate per member. Record format is invariant across members, so there is no schema divergence to handle in binding or SQLDA descriptors. The only behavior left to define is precedence against an active OVRDBF, and the natural rule is that the more specific wins: an explicit clause in the statement overrides any active override. That is a documentation decision, not a technical obstacle.

In short, this request does not add a concept to Db2 for i — it removes an asymmetry that currently keeps member-partitioned data outside the reach of standard SQL.

SQL may stand for Structured Query Language, but after many years IBM i, I've learned that "Structured" definitely doesn't mean "Standard". 😉

Since multi-member physical files are one of those uniquely IBM i features, allowing SQL to query a specific member directly would make perfect sense.

The CAAC has reviewed this IBM Idea and recommends that IBM view this as a high priority Idea that is important to be addressed.

Background: The COMMON Americas Advisory Council (CAAC) members have a broad range of experience in working with small and medium-sized IBM i customers.  CAAC has a key role in working with IBM i development to help assess the value and impact of individual IBM Ideas on the broader IBM i community and has therefore reviewed your Idea.

For more information about CAAC, see www.common.org/caac

Brandon Pederson - CAAC Program Manager

Portal Public
IBM will use this Idea as input to planning, but no commitment is made or implied. This Idea will be updated in the future if IBM implements it.

Db2 for i development team
IBM Power Systems Development

Excelente propuesta! Para no crear mas alias! 

I think this is a great idea 

I like this idea, please add

I would very much appreciate if this idea get implemented. Very useful 

I like this idea, because it is cumbersome to use SQL ALAIS.

Excelente propuesta que llevamos pidiendo desde V2