Partitioned column value.


Partitioned column value.

I have the requirement to retrieve the indexes and i retrieved it through by joining the two dictionary data tables DBC.columns and DBC.indices. I have to get the some unique value that is declared it in the partition by range_n - column [YEAR_MO_KEY]

PARTITION BY RANGE_N(year_mo_key BETWEEN 1285 AND 1307 EACH 1 ,
1308 AND 1308 EACH 1 ,
1309 AND 1309 EACH 1 ,
1310 AND 1310 EACH 1 ,
1311 AND 1311 EACH 1 )

Please suggest me how to retieve the YEAR_MO_KEY value from data dictionary values or any sub queries to retrieve the data. I can check the databases what are all the tables partitioned by DBC.indexconstraints in that i could not find it out any separate column declared for YEAR_MO_KEY and but it is declared it in constrainttext column of the dbc.indexconstraint table.

Please guide me

Re: Partitioned column value.

How to trim this value using LTRIM and RTRIM


and i need only year_mo_key value

Re: Partitioned column value.

below query should meet UR requirement

SELECT substr(ConstraintText, 17, index(ConstraintText,'BETWEEN') -17 )
FROM DBC.IndexConstraints t
WHERE ConstraintType = 'Q'
AND ( SUBSTRING(ConstraintText FROM 1 FOR 13) < 'CHECK (/*02*/'
OR SUBSTRING(ConstraintText FROM 1 FOR 13) > 'CHECK (/*15*/' )
ORDER BY DatabaseName, TableName;

Re: Partitioned column value.

I have been looking for answers to a basic question on table partitioning in Teradata. Can someone please shed more light. Here it is -

If we do Range Partitioning on a table based on Numeric values and then different jobs load data corresponding to different partition ranges in parallel - then do we get into a resource contention scenario OR do all parallel running jobs hit specific partition and only acquire lock on a particular partition. In short - the question is - while loading data into a partitioned table [in this case Range Partition], do we acquire a lock at the partition level and multiple programs can run in parallel acquiring lock at individual partitions OR the lock is acquired at whole table level and the parallel jobs get into a contention mode.

Ex: An error table where each program loads specific error information that is encountered based on error code. If the table is range partitioned on error codes of range of 1000s, will individual programs when run in parallel hit their specific partition acquiring only lock to it or they will acquire complete table lock and release them for subsequent jobs when error load is done.

Please advice.