April 2015 Entries

These general statements will get you the information that you're looking for:


SELECT
TABLE_CAT,
TABLE_SCHEM,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
TYPE_NAME,
COLUMN_SIZE,
COLUMN_TEXT

FROM "SYSIBM"."SQLCOLUMNS"
WHERE TABLE_SCHEM = 'YourSchema'
 AND TABLE_NAME = 'YourTableName'

This will get you the definitions of the columns:

SELECT
t.table_schema as Library
,t.table_name
,t.table_type
,c.column_name
,c.ordinal_position
,c.data_type
,c.character_maximum_length as Length
,c.numeric_precision as Precision
,c.numeric_scale as Scale
,c.column_default
,t.is_insertable_into
FROM sysibm.tables t
JOIN sysibm.columns c
   on t.table_schema = c.table_schema
  and t.table_name = c.table_name
WHERE t.table_schema = 'YourSchema'
and t.table_name = 'YourTableName'
order by t.table_name, c.ordinal_position
Posted On Wednesday, April 22, 2015 4:15 PM | Comments (0)
Filed Under [ DB2 ]
Go to the server your are interested in gathering the information from, then issue this statement:

WITH LastRestoresOnServer AS
(
SELECT
    sysdb.[name] as dbName,
    sysdb.[create_date],
    sysdb.[compatibility_level],
    sysdb.[collation_name],
    r.*,
   ROW_NUMBER() OVER (PARTITION BY sysDb.Name ORDER BY r.[restore_date] DESC) as RN
FROM master.sys.databases sysdb
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = sysDb.Name
)
SELECT *
FROM [LastRestoresOnServer]
WHERE RN = 1

You were probably trying to do a comparison between 2 databases with different collation settings on a WHERE clause or on a  join. Here's how you would do the comparison with a JOIN:

Select * 
from source db1.schema.table1 as t1
join source db2.schema.table2 as t2
on t1.col1
collate SQL_Latin1_General_CP1_CS_AS = t2.col1 COLLATE SQL_Latin1_General_CP1_CS_AS