Finding object/database ID of a Netezza database

There are two ways you can find out object/database ID of a Netezza database.

Please note the below examples are run on pre-7.0.3 environment where we don’t have multi-schema support. However, the same commands works on 7.0.3 onwards too.

(1) Way1:

[nz@nzdev2a 1]$ nzsql

Welcome to nzsql, the IBM Netezza SQL interactive terminal.

Type:  \h for help with SQL commands

\? for help on internal slash commands

\g or terminate with semicolon to execute query

\q to quit

SYSTEM(ADMIN)=> \d  _v_database

View “_V_DATABASE”

Attribute     |              Type               | Modifier | Default Value

——————+———————————+———-+—————

OBJID            | OID                             |          |

DATABASE         | NAME                            |          |

OWNER            | NAME                            |          |

CREATEDATE       | ABSTIME                         |          |

DB_CHARSET       | NAME                            |          |

DB_COLLATION     | NAME                            |          |

DBCHARSET        | OID                             |          |

DBCOLLATION      | OID                             |          |

DBOWNERID        | OID                             |          |

DBLOCKPID        | INTEGER                         |          |

DBSTATUS         | NATIONAL CHARACTER VARYING(255) |          |

BACKUPGROUP      | NAME                            |          |

OBJDELIM         | BOOLEAN                         |          |

DBCOLLECTHISTORY | BOOLEAN                         |          |

ENCODING         | INTEGER                         |          |

View definition: SELECT OD.OBJID, OD.OBJNAME AS “DATABASE”, OD.”OWNER”, OD.CREATEDATE, CASE WHEN (“d”.ENCODING = 0) THEN “c”.COLCHARSET ELSE “NAME”((‘IBM’::”VARCHAR” || “VARCHAR”(“d”.ENCODING))) END AS DB_CHARSET, “c”.COLCOLLATION AS DB_COLLATION, “d”.DBCHARSET, “d”.DBCOLLATE AS DBCOLLATION, OD.DBOWNER AS DBOWNERID, “d”.DBLOCKPID, CASE WHEN (“d”.DBLOCKTYPE = ‘U’::”CHAR”) THEN (‘READ-ONLY’::”NVARCHAR”)::NVARCHAR(255) WHEN (“d”.DBLOCKTYPE = ‘E’::”CHAR”) THEN (‘EXCLUSIVE’::”NVARCHAR”)::NVARCHAR(255) ELSE ‘NULL’::”NVARCHAR” END AS DBSTATUS, GO.OBJNAME AS BACKUPGROUP, OD.OBJDELIM, “d”.DBCOLLECTHISTORY, “d”.ENCODING FROM (((ADMIN.”_V_OBJ_DATABASE” OD JOIN ADMIN.”_T_DATABASE” “d” ON ((“d”.OID = OD.OBJID))) LEFT JOIN ADMIN.”_T_COLLATION” “c” ON ((“c”.OID = “d”.DBCOLLATE))) LEFT JOIN ADMIN.”_T_OBJECT” GO ON (((“d”.DBBACKUPGRP NOTNULL) AND (“d”.DBBACKUPGRP = GO.OBJID))));

 

SYSTEM(ADMIN)=> select OBJID, DATABASE, OWNER from _v_database;

OBJID  | DATABASE  | OWNER

——–+———–+——-

219958 | EDWQA     | ADMIN

218470 | EDWQAHIST | ADMIN

2 | MASTER_DB | ADMIN

1 | SYSTEM    | ADMIN

(4 rows)

 

(2) Way2

[nz@nzdev2a 1]$ nzstats -type database

DB Id  DB Name   Create Date         Owner Id Num Tables Num Views Num Active Users

—— ——— ——————- ——– ———- ——— —————-

1 SYSTEM    2013-01-15 16:50:16      500          0         0                6

2 MASTER_DB 2013-01-15 16:50:16      500          0         0                0

218470 EDWQAHIST 2013-01-15 17:32:53      500         35         0                0

219958 EDWQA     2013-01-16 01:33:20      500       1006       241                0

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: