These are attributes of one table that have matching values in a primary key in another table, allowing for relationships between tables. So, if the unique index is defined on not null column(s), then it is essentially the same as the Primary Key and can be treated as an alternate key meaning it can also serve the purpose of identifying a record uniquely in the table. This is just the default behavior though and can be changed at creation time, if needed. There can be only one Primary Key defined on the table where as you can have many unique indexes defined on the table (if needed).Īlso, in the case of SQL Server, if you go with the default options then a Primary Key is created as a clustered index while the unique index (constraint) is created as a non-clustered index. That is one of the things to look out for when designing/developing/porting applications across RDBMS. A note worth mentioning over here is that different RDBMS treat this differently –> while SQL Server and DB2 do not allow more than one NULL value in a unique index column, Oracle allows multiple NULL values. The column(s) that make up the unique index can be nullable. What is the difference between a Primary Key and a Unique Index?īoth can be declared on one or more columns, both can be used to enforce foreign keys (if the unique index is on not null column(s)), both can be declared as clustered/non clustered indexes (SQL Server lingo), both can be used on computed columns as well (SQL Server).Ĭolumn(s) that make the Primary Key of a table cannot be NULL since by definition, the Primary Key cannot be NULL since it helps uniquely identify the record in the table.
![mainframe refresher by muthu mainframe refresher by muthu](https://demo.pdfslide.net/img/380x512/reader024/reader/2021021818/5527578449795971178b465b/r-1.jpg)
SQLCODE if BIND not performed, SQLCODE if table is missing What are the factors you should consider while creating a new DB2 tableĭeadlock in Db2 and work arounds to avoid deadlocksĭifferent SQLcodes that you should handle in COBOL programs Is it a good practice to run DB2 utilities in the same job (eg UNLOAD/RELOAD) ? What about the normal JCL utilities (SORT,IDCAMS,IEFBR14 etc) ?
#MAINFRAME REFRESHER BY MUTHU HOW TO#
what factors we need to keep in mind.ĥ) How to resolve Db2 deadlock situation.Ħ) What is cursor option “with hold” meanħ) What are the outcomes and parameters for EXPLAIN reports 803, -805, -811 (multiple rows found), -818(timestamp mismatch), -911 – Deadlock with rollback occurredġ) What are the process to be followed when you are adding a new table.Ģ) How to determine when the commit needs to be done. What is isolation level and which parameter is preferred in it and why?Īn isolation level specifies how much one transaction is isolated from other transactions in a multiple-connection environment. What is deadlock and ways of avoiding it.ģ. How do you design a table? What are the different consideration for this.Ģ.
![mainframe refresher by muthu mainframe refresher by muthu](https://cdn.slidesharecdn.com/ss_thumbnails/1bf44b9e-54a0-4f7c-ae29-f3695d37ec6c-160105143323-thumbnail-4.jpg)
Office CHAR(128) NOT NULL WITH DEFAULT ‘Dallas’,Īmt DECIMAL(10,2) NOT NULL CHECK (amt > 99.99)ġ. (po_number INTEGER NOT NULL CONSTRAINT uc1 UNIQUE, Explain statement? something acees strategy –ĭefine MAX value for the column, -1 in NULL Indicator. one more question on with ur what it prevents in production…ġ5. with UR what does it means? Uncommitted Readġ4.
![mainframe refresher by muthu mainframe refresher by muthu](https://assets.kit.co/kits/8c/52/panunumpa-ng-kawani-ng-dpwh-27-cherbart-8c5292294975defff9f9f502cec3e6ce.jpg)
group by clause – GROUP BY partitions the selected rows on the distinct values of the column on which you group by.ġ3.
#MAINFRAME REFRESHER BY MUTHU UPDATE#
1) What is outer join and inner join? Left outer join exampleĢ) How do you use and implement sub-selects?ģ) How do you ensure a restart logic in the event that the program abends after processing only partial records?Ĥ) What is the need/use of a PLAN from the Application programmer perspective?ĥ) How do you check the performance of your DB2 program?ħ) If you are using a Db2 cursor with For update clause and commits statements, which parameter needs to be included to prevent the cursor from closing out?Ĩ) What is the difference between primary keys and indexes?ġ0) What is the difference between UNION and UNION ALL?ġ1) What is the use of plan_table, what information it contains?Ħ.concate first name and last name in query – SELECT FIRSTNAME || LASTNAME FROM EMP_TABLEħ.-911 – Deadlock occurred and rollback has been doneĨ.UNION UNION ALL in SQL? – The main difference is that UNION will eliminate any duplicate rows returned by the various select statements being unioned whereas UNION ALL will not.ĩ.IN or BETWEEN – BETWEEN supplies a range of values while IN supplies a list of values.