PowerDesigner: Write Netezza distribution type via VBScript

Question

I'm working with PowerDesigner 16.5 and I'm making a physical model for Netezza 5.0.

I need to place a 'Distribution Type' in the 'Options' tab for each table, which is naturally very important. I'm however looking for a more organized way to place my distribution type and the list of columns.

I would like to enter this information via a VBScript. I'm however having trouble to find the correct command to actually address these options via the script. I would normally use the 'GetPhysicalOptionValues' but the 'Distribution Type' doesn't actually seem to be a physical option. It is a normal option. The documentation for PowerDesigner doesn't seem to be much help.

TL;DR: Looking to script my Netezza distribution keys via VBScript. Can't find right variable.

Answer 1

You shoul edit the DMBS and include in Script/Objects/Table a new Text Item named SqlDistributeOption containing this:

SELECT   t.owner,t.tablename, 
    CASE 
        WHEN (m.relid isnull) THEN 'distribute on random '
        ELSE 
            CASE 
                WHEN (m.distseqno = 1) THEN 'distribute on hash ('
                ELSE ', ' 
            END 
                || m.attname 
                || 
            CASE 
                WHEN ( m.distseqno =
                        (
                          SELECT max(n.distseqno)
                          FROM _v_table_dist n
                          WHERE n.relid = m.relid)) THEN ')'
                ELSE '' 
            END 
    END 
FROM   _v_table t 
left outer join _v_table_dist m ON (m.relid = t.objid) 
WHERE   1 = 1 [ 
AND    t.owner = %.q:OWNER%] [ 
AND    t.tablename = %.q:TABLE%] 
ORDER BY  t.objid, m.distseqno

That should do it, at least in my case

Regards

Answer 2

Your solution set me on the right path. It also helped me find these: SetExtendedAttribute("Distribution","hash") SetExtendedAttribute("DistributionColumnList","ColumnName")