Constraints information in the database catalog in DB2

By -
Table 1. Constraints information in the database catalog

Catalog viewView columnDescriptionQuery example
SYSCAT.CHECKS
Contains a row for each table check constraintdb2 select constname, tabname, text from syscat.checks
SYSCAT.COLCHECKS
Contains a row for each column that is referenced by a table check constraintdb2 select constname, tabname, colname, usage from syscat.colchecks
SYSCAT.COLUMNSNULLSIndicates whether a column is nullable (Y) or not nullable (N)db2 select tabname, colname, nulls from syscat.columns where tabschema = 'DELSVT' and nulls = 'N'
SYSCAT.CONSTDEP
Contains a row for each dependency of a constraint on some other objectdb2 select constname, tabname, btype, bname from syscat.constdep
SYSCAT.INDEXES
Contains a row for each index.db2 select tabname, uniquerule, made_unique, system_required from syscat.indexes where tabschema = 'DELSVT'
SYSCAT.KEYCOLUSE
Contains a row for each column that participates in a key defined by a unique, primary key, or foreign key constraintdb2 select constname, tabname, colname, colseq from syscat.keycoluse
SYSCAT.REFERENCES
Contains a row for each referential constraintdb2 select constname, tabname, refkeyname, reftabname, colcount, deleterule, updaterule from syscat.references
SYSCAT.TABCONST
Contains a row for each unique (U), primary key (P), foreign key (F), or table check (K) constraintdb2 select constname, tabname, type from syscat.tabconst
SYSCAT.TABLESPARENTSNumber of parent tables of this table (the number of referential constraints in which this table is a dependent)db2 "select tabname, parents from syscat.tables where parents > 0"
SYSCAT.TABLESCHILDRENNumber of dependent tables of this table (the number of referential constraints in which this table is a parent)db2 "select tabname, children from syscat.tables where children > 0"
SYSCAT.TABLESSELFREFSNumber of self-referencing referential constraints for this table (the number of referential constraints in which this table is both a parent and a dependent)db2 "select tabname, selfrefs from syscat.tables where selfrefs > 0"
SYSCAT.TABLESKEYUNIQUENumber of unique constraints (other than primary key) defined on this tabledb2 "select tabname, keyunique from syscat.tables where keyunique > 0"
SYSCAT.TABLESCHECKCOUNTNumber of check constraints defined on this tabledb2 "select tabname, checkcount from syscat.tables where checkcount > 0"

Comments

Post a Comment

Popular posts from this blog

Struts 2 : Warning :No configuration found for the specified action: 'Login.action' in namespace: '/'

By -
No configuration found for the specified action: 'Login.action' in namespace: '/'''. Form action defaulting to 'action' attribute's literal value. Solution: To remove this warning you need to change following... See your struts.xml file what name you have specified... If you are using namespace / and your action name is 'Login' then  replace Login.action to Login as given below... <s:form action=" Login.action " method="post" >   Use below with namespace  1) <s:form action=" Login " method="post" namespace="/">   Or   2) <s:form action=" /Login " method="post" >   Now you will not get this warning.
Read more

Upload csv file at Google Cloud Storage using java

By -
First do all required pre requiste as per google doc https://cloud.google.com/appengine/docs/java/googlestorage/ Now see the sample code for a csv file uploader using latest api public String uploadFileUsingGCSClient(String csvDate, String fileName, String dirName,String bucketName) throws IOException {  // init the bucket access     GcsService gcsService = GcsServiceFactory.createGcsService(RetryParams.getDefaultInstance());     GcsFilename filename = new GcsFilename(bucketName+"/"+dirName, fileName);     GcsFileOptions fileOptions = new GcsFileOptions.Builder()    .mimeType("application/CSV")       .acl("public-read")    //.addUserMetadata("myfield1", "my field value")    .build();     GcsOutputChannel outputChannel = gcsService.createOrReplace(filename, fileOptions);          // write file using this st...
Read more

Read Images from a xlsx file using Apache POI

By -
This article is all about how to read and download images inside a file in xlsx format. We will use Apache POI using java. 1. Maven dependencies commons-logging-1.2.jar  commons-collections4-4.1.jar openxml4j-1.0-betapoi-3.16.jar poi-ooxml-3.16.jar poi-ooxml-schemas-3.16.jar xmlbeans-2.6.0.jar servlet-api 2. Sharing java code import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Iterator; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import org.apache.poi.ss.usermodel.PictureData; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFDrawing; import org.apache.poi.xssf.usermodel.XSSFPicture; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFShape; import org.apache.poi.xssf.usermodel.XSSFSheet; imp...
Read more