Question:- What is the difference between CS and RR isolation levels?
Answer:- CS would release the lock on the page after its use. RR would retain all the locks acquired till the end of a transaction.
Question:- What is meant by Lock Escalation?
Answer:- Lock escalation is the process of promoting page lock sizes to table or table space lock size when the transaction has acquired more locks than the ones specified in NUMLKTS. Locks have to be taken on objects in a single table space for escalations to take place.
Question:- What are the various types of Locks?
Answer:- There are three different types of locks: SHARE, EXCLUSIVE, and UPDATE.
Question:- What is ALTER?
Answer:- ALTER is the SQL command used to change the definition of DB2 objects.
Question:- What do you understand by DBRM and PLAN?
Answer:- DBRM denotes Database Request Module. It has the SQL statements that are extracted from the host language program obtained by the pre-compiler. PLAN is the result of the BIND process and has executable code for SQL statements in DBRM.
Question:- What is meant by ACQUIRE/RELEASE in BIND?
Answer:- ACQUIRE/RELEASE in BIND determines the point at which DB2 either acquires or releases locks against the table and the table spaces. This includes the intent locks.
Question:- What is meant by PACKAGES?
Answer:- PACKAGES contain executable codes for SQL statements in respect of one DBRM.
Question:- What are the advantages of using PACKAGES?
Answer:- When used, PACKAGES help avoid binding of large numbers of DBRM members in one plan. They also dispense with the cost of large BIND and avoid the entire transactions making them unavailable during BIND and automatic REBIND of the plan. Another advantage is that they minimize the fallback complexities when changes result in an error.
Question:- What is a collection?
Answer:- A collection is a user-defined name that works as the anchor for packages but has no physical existence. It is used for the grouping of packages.
Question:- What is Dynamic SQL?
Answer:- Dynamic SQL is the SQL statement created at the time of the execution of a program.
Question:- Compare SQL Server with Oracle.
Answer:- SQL Server • Windows and Linux • T-SQL (Transact-SQL) • Simpler and easy to use • Simple Oracle • Windows, Solaris, Linux, and Unix • PL/SQL (Procedural Language/ SQL) • Complex but powerful • Complex
Question:- How can SQL Server instances be hidden?
Answer:- To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this, we have to follow the below steps: • First, in SQL Server Configuration Manager, we have to expand ‘SQL Server Network Configuration’ • Right-click on Protocols for and select ‘Properties’ • Once we do that, we will find a ‘HideInstance’ box in which, on the ‘Flags’ tab, we have to select ‘Yes’ • After that, click on ‘OK’ Note: While hiding a named instance, we need to provide the port number in the connection string so that even if the browser is running it is possible to connect to the hidden instance.
Question:- Can we add a CPU to SQL Server?
Answer:- Yes. we can add CPUs physically by adding new hardware, either logically by online hardware partitioning or virtually through a virtualization layer. Starting with its 2008 version, SQL Server supports CPU Hot Add. There are a few requirements to use CPU Hot Add: • Hardware that supports CPU hot add • 64-bit edition of Windows Server 2008 Datacenter or Windows Server 2008 Enterprise Edition for Itanium-based system OS • SQL Server Enterprise Once the CPU is added, we need to run RECONFIGURE, and then SQL Server recognizes the newly added CPU.
Question:- How can we check whether the port number is connecting or not on a Server DBA?
Answer:- TELNET PORTNUMBER TELNET PAXT3DEVSQL24 1433 TELNET PAXT3DEVSQL24 1434 Common Ports: MSSQL Server: 1433 HTTP TCP 80 HTTPS TCP 443