Hello. Facing issues with lookup for db2 tables. I'd like to provide a list of db2 tables for compliance check and using deftype in formula basefield:type.keyfield.targetfield

I'm following CARLa Command Reference documentation page 63, example:

deftype type=email
define type=email user as word(record,1,' ')
define type=email address as word(record,2,' ')

SMFUSER:EMAIL.USER.ADDRESS

Unfortunately, struggling to pick up the names of db2 tables for both db2_access and db2_table types.

Is this something related to above types not available for lookup function? 

Hi Viktorija,

the example LOOKUP that you refer to is a so called DEFTYPE LOOKUP. A DEFTYPE newlist is a self-defined newlist type that you can define for an input source (e.g. an external data set) for which CARLa does not support a standard newlist type. The fields in this DEFTYPE newlist can be retrieved with a LOOKUP function when the BASE field (SMFUSER in the example) matches the field USER in DEFTYPE newlist EMAIL, CARLa can retrieve the value that is stored in field ADDRESS. 

However, the newlist types that you mention DB2_ACCESS and DB2_TABLE are supported NEWLIST types and behave different as DEFTYPE newlist types. To retrieve fields from DB2_ACCESS and/or DB2_TABLE a general LOOKUP type is required. But a general LOOKUP type is supported for a limited number of source NEWLISTs to a limited number of target NEWLISTs. 
When you read on in the CARLa Command Reference about General Lookup (starting on page 67), you encounter 2 tables that document supported general LOOKUPs:

• Table 14. Supported sources for an explicit lookup operation to target newlists CLASS, SYSTEM, SETROPTS, ACF2_LID, and ACF2_FDE
• Table 15. Supported sources for an explicit lookup operation to target newlists AS, DB2_REGION, ICSF_SYMKEY, ID, MQ_REGION, and SPT

Unfortunately, currently there's no support for general LOOKUP that have target NEWLIST type DB2_ACCESS and DB2_TABLE.

Perhaps you can circumvent this restriction, by first generating a report for all Db2 table names from DB2_ACCESS and DB2_TABLE and write the output to a work data set.  Next, you can define your own DEFTYPE newlist for the list of Db2 table names that you can then use in a DEFTYPE LOOKUP.

------------------------------
Tom Zeehandelaar
z/OS Security Enablement Specialist - zSecure developer
IBM
------------------------------

Hi Viktorija

It would help a lot if you could show us the CARLa code that you want to use, even if the code doesn't work yet.  A lookup has a base and a target, that means, resp., the field before and the field after the colon.  Do you want to use the table name as base, or as target?

An alternative to lookup is a defined field, that you define in a separate member.  Something like

define type=db2_table critical_table("CRIT",4,hb) boolean where name=(, 
abc.**.
efg.klm.*,
)

In your type=db2_table compliance check you would reference the field name critical_table as a normal field.

After a zoom meeting, Viktorija and I established that the NAME field in a newlist type DB2_TABLE is not suitable for use in a lookup.  Like many fields in the DB2 related newlists, it has UTF8 information (as evidenced with the FIELDS primary command in zSecure ISPF), and indirect reference (lookup) requires a character formatted base field (DEFINE command).  So lookup from DB2 newlists is ... complicated.

newlist type=db2_table
  sortlist name name:deftype.tablename.tableattribute,  /* won't work, name is UTF8 */
   schema schema:dfltgrp  /* must test that schema refers to an existing user ID, but won't work */

To address the requirements we generated DEFINE commands in a separate CKRCARLA step, with all the user IDs in the system, and use this as a flag to identity unresolved SCHEMA values.  CARLa similar to

newlist type=racf nopage dd=ckr2pass
   define each(nd) boolean where complex==complex
   define once(nd) count
   select class=user segment=base
   summary once "define type=db2_table schemaContainsUserID true where schema=(,"
   summary each key(0) | ","
   summary once ")"

results in 

define type=db2_table schemaContainsUserID true where schema=(,
userid1,
userid2,
)

Imbed this define in your reporting CARLa, use NOT(schemaContainsUserID) for selection of DB2_TABLE and Bob's your uncle.

------------------------------
Rob van Hoboken
------------------------------

whoa!
Lookup from the NAME or SCHEMA field to a DEFTYPE defined table WORKS... provided the external DEFTYPE file contains UTF8 (ASCII) data in the target column.  So (kludge warning), if you need a translation or table lookup from a UTF8 field, create the external file using FILEOPTION ENCODING=UTF-8, and print the result of the lookup using the UTF8 output format.  This is not the behavior I expect from reading the manual.  Shudder.