Table Structure in SQLCI Tandem.

[Kot]

Hi Everyone,

Is there a way to extract the Table structure (Field name,Data type) in SQLCI Tandem,similar to the below one.

SELECT NAME,COLTYPE,NULLS,LONGLENGTH FROM SYSIBM.SYSCOLUMNS where TBcreator =SCHEMANAME and TBNAME =TABLENAME;

Thanks in Advanvce..

[Robert Hutchings]

Yes, look at the TABLES table in your SQL directory...$xxxx.catalog.*
(is that where it is these days?)

[Tone]

Have a read of the CATALOGS entry in the SQL/MP Reference Manual, and
then the COLUMNS entry.

A FUP INFO <tablename>,DETAIL will show what catalog a table is
registered in.

Then in SQLCI

SELECT COLNAME,DATATYPE FROM COLUMNS
WHERE TABLENAME = "\SYS.$VOL.SUBVOL.TABLE";

[Keith Dick]

The above answers are directly to the point of your question. You might have to use

WHERE TABLENAME LIKE "\SYS.$VOL.SUBVOL.TABLE%"

instead of what Tone wrote, but try using the approach he shows first -- it might be right, but if it doesn't work, try the LIKE approach. I can't remember whether the LIKE approach is needed or not. Also, if getting the catalog by doing FUP INFO DETAIL is not suitable for your needs, there is a FILE_GETINFOBYNAME call that can get you the catalog name for a SQL/MP table. If you need to do that and can't figure it out, ask here about it and someone can tell you the details.

If getting the information from the catalog is more programming than you want to do, you could use an INVOKE command. That shows more than you asked for, but if you can ignore the parts you don't want, that might be easier for you to use. Depends on your needs.

[wbreidbach]

If you just want to get the structure of the table within SQLCI, just do
SQLCI
invoke <tablename>;

Invoke will give you all the fields with description.

[Tone]

Doh!! Of course. Too hung up on using select.

[Frans Jongma]

It depends if you want to see the row-layout (use INVOKE) or want to see
the DDL of the table.
The DDL can be obtained by SHOWDDL using the SQL/MX engine.

Use mxci or a JDBC tool and use showddl.

SHOWDDL $fc201.fjsql.mptab, sqlmp;
CREATE TABLE \TAURUS.$FC201.FJSQL.MPTAB
(
COLA INT NO DEFAULT NOT NULL
, COLB CHAR(10) CHARACTER SET UNKNOWN NO DEFAULT
NOT NULL
, PRIMARY KEY (COLA ASC)
)
CATALOG \TAURUS.$FC201.FJSQL
SECURE "OOOO"
;

--- SQL operation complete.

[Tone]

True, but the op mentioned SQLCI so we've assumed SQL/MP.

[Bill Honaker]

But the OP also mentioned Tandem and there's a high probability it's not that old. :-)

[Keith Dick]

Note the ",sqlmp" option on that SHOWDDL command in Frans' example -- it is working on a SQL/MP table. SQL/MX had an explicit requirement to work with SQL/MP tables. This is one example of that.

[Kot]

Thank you All.Able to view the Table structure :-)

Thank you All.Able to view the Table structure :-)

[swati....@gmail.com]

Kot, can you tell what command you used. I am new to TACL or Tandem. I need to find out what tables are there. I know how to use INVOKE command but in order to perform any query I should know the table name which i don't know how to find out.

[rkd...@gmail.com]

For SQL/MP tables, you can use a number of commands. The simplest probably is the TACL FILEINFO command:

FILEINFO pattern

where pattern is a filename pattern -- something like $DATA1.DB.* -- which would list the names and some attributes of all the files and tables in the $DATA1.DB subvolume (the * matches any file or table name).

You can get a bit more selective using either FUP or SQLCI to show information about files or tables whose names match a pattern and also have attributes that match further conditions. For example, the SQLCI command FILEINFO could be used:

SQLCI
FILEINFO $DATA1.DB.* WHERE EOF > 1000000;

would list all the files or tables in $DATA1.DB whose EOF is larger than 1000000. You can tell which of the names listed are SQL/MP tables by looking in the TYPE column of the output listing. A SQL/MP table will include "Ta" in that column.

Or you can ask FILEINFO to select only SQL objects:

SQLCI
FILEINFO $DATA1.DB.* WHERE SQL AND EOF > 1000000;

would list all the files or tables in $DATA1.DB that are SQL objects and have EOF greater than 1000000.

To see what can follow WHERE in those commands, look up "qualified fileset" in the manual.

Filename patterns can be more complex than the simple one I've used in these examples.

If you are asking about finding SQL/MX tables, the going is a bit harder. At least the last I was aware of, the mxci utility did not have a simple command that would list tables whose names matched a pattern. There are utilities that run on a PC, accessing the NonStop system via JDBC, that can show lists of SQL/MX catalogs, schemas, and tables. I'm not very familiar with them. You also can run SELECT statements in mxci referencing catalog and schema tables to list data table names, but those SELECT statements are a bit complex and I don't remember them off the top of my head. If it is SQL/MX tables whose names you need to find, post again saying that is what you need, and I or someone else will make some suggestions about doing that.

[Randall]

The most effective way to get a table structure is to read the catalog's metadata. This are well documented in the SQL/MP and SQL/MX Reference Manuals. You can use SQL SELECT operations to get at all the data that SQL itself uses for querying and managing table structures.

Cheers,
Randall