By following Bob ward’s “Inside tempdb” session in the SQL SUMMIT 2011. I want to try to reproduce the tempdb log full and cause the SQL Server shutdown.scenario.
My test version of SQL Server is 2008R2.
Here are the steps to reproduce the error
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE =1034KB,maxsize=1024KB, FILEGROWTH = 0 )
GO
sp_helpdb tempdb
Today, I was testing something in the SQL Server and while trying to create the database, I got below error.
Msg 1807, Level 16, State 3, Line 2
Could not obtain exclusive lock on database 'model'. Retry the operation later.
Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
We all know that the model database is used for creating the database. If you want to have some objects being part of new database, we can put in the model. During the database creation, sql serve would copy the model and create the new one and then run the alter database to make to the size you specify in the create database syntax.
However, it is new to me that the create database statement require hold the exclusive lock on the model db, it does make sense so because we don’t want other processes is changing the model database while we are creating the new database.
To reproduce this error, just open any process and issue ‘use model’ and in another session , run the create database statement.
In below screenshot, the first top output is from sp_who2, you can see, spid 54 is in mode database and it is blocking the spid 54 ‘Create database’.
The 2nd part of output is from sys.dm_os_waiting_tasks, the spid 60 is waiting for the LCK_M_X . LCK_M_X is the exclusive locks .
Sqldummper.exe is the SQL Server utility that can take the memory dump for the SQL server. After taking the memory dump, we can load it using the winDbg to see the internal call for trouble shooting.
Sqldummper comes with the sql server install and you can find it in the common shared folder.
SQLServerInstallDrive:\Program Files\Microsoft SQL Server\number\Shared
Note In this folder path, number is a placeholder for one of the following:
Here is the example to take the mini memory dump and put the dump file in the C:\temp
My last attempt to create the NUMA lab environment with Aamazon EC2 was successful . ( see pot here). However, it will cost 7.25 per hr.
During the research of NUMA, I found the article by Jonathan Kehayias. He use BCDEdit to fake the hardware NUMA. This seems very interesting so I decide to give it a try.
My VM environment is Virtual Box, I was not sure it can work because it does not let me specify number of core and cpu like VMWare does.
If the VM is VMware workstation, it let us specify the number of core and CPU.
In my last post, I create my MSSQL instance on the AWS RDS. This time, I am going to try hosting the SQL Server on the EC2. One of the major difference between EC2 and RDS is, in RDS, we only get the instance. In EC2, We have access the Host.
Amazon provide the platform which allow user to set up the SQL Server very quickly …. Today, I decided to give it a try and feel it is too easy to be true .
First, you have to have the AWS account being created.
db2 "DECLARE MYCURS1 CURSOR FOR SELECT * FROM DEPARTMENT"
db2 LOAD FROM MYCURS1 OF CURSOR INSERT INTO DB2DEMO.DEPARTMENT
db2 "DECLARE MYCURS2 CURSOR DATABASE sample10 user db2inst1 using XXXXX FOR SELECT * FROM DEPARTMENT"
db2 LOAD FROM MYCURS2 OF CURSOR INSERT INTO DB2DEMO.DEPARTMENT
http://www.ibm.com/developerworks/data/library/techarticle/dm-0901fechner/
DB2 export utility can export the table data to 4 different file format:
Below example is to export the file as binary format.
db2 export to /tmp/department.ixf of ixf messages /tmp/export_department.log "select * from department"
The SQL statement must be in quote, otherwise, it will get the “SQL3022N An SQL error "-104" occurred”
Below example is to export the file as DEL format , by default it is separate by common separate .
db2 export to /tmp/department.del of del messages /tmp/export_del_department.log "select * from department"
Below example is to import the IXF data and create the table.
db2 import from /tmp/department.ixf of ixf messages /tmp/import_ixf_department.log "create into department2"
The data looks good but index does not get created and the error is showing in the message file.
Below example is to import the del data with REPLACE
db2 import from /tmp/department.del of del messages /tmp/import_del_department.log "replace into department2"
Below example is to load IXF data and replace existing data.
db2 load from /tmp/department.ixf of ixf messages /tmp/load_ixf_department.log replace into department2
Below example is to load IXF data and insert into the table.
db2 load from /tmp/department.ixf of ixf messages /tmp/load_ixf_department.log insert into department2
Below example is to load DEL data and insert into the table.
db2 load from /tmp/department.del of del messages /tmp/load_del_department.log insert into department2
Another character is the load can replace existing data even there is a dependence for other table. Import can not replace the existing data if other tables has dependence on it.
-- Below would workdb2 load from /tmp/department.ixf of ixf messages /tmp/load_ixf_department.log replace into department
db2 “select tabname,status from syscat.tables where tabname='DEPARTMENT' and tabschema='DB2INST1'”
db2 “SET INTEGRITY for DEPARTMENT,EMPLOYEE IMMEDIATE CHECKED”
--- Below would fail becuase department table has index, and constraint that is refence by other tabledb2 import from /tmp/department.del of del messages /tmp/import_del_department.log "replace into department"
The load log shows the index would be rebuild after the load .
If the table reach an unstable state due to incomplete loading. We can use below statement to take out the unstable state.
db2 load client from /dev/null of ixf messages /tmp/xxx terminate into department2
Package is the logical grouping for the resource that require to execute the routine . It is also known as “Access plan” . Rebind package is the same concept as recompile in other DBMS ( MSSQL, Sybase and Oracle).
--- ALl packages: db2 list package for all
select pkgname, pkgschema, pkgversion, unique_id, boundby,reoptvar, total_sect,
valid, format, isolation, blocking
from syscat.packages
order by pkgschema, pkgname, pkgversion;
--- Package own by NULLID schema: db2 list package for system
select pkgname, pkgschema, pkgversion, unique_id, boundby,reoptvar, total_sect,
valid, format, isolation, blocking
from syscat.packages
where pkgschema = 'NULLID'
order by pkgschema, pkgname, pkgversion;
-- Packages own by user: db2 list package
select pkgname, pkgschema, pkgversion, unique_id, boundby,reoptvar, total_sect,
valid, format, isolation, blocking
from syscat.packages
where pkgschema = USER
order by pkgschema, pkgname, pkgversion;
REOPTVAR: Indicates whether the access path is determined again at execution time using input variable values.
SELECT PKGNAME, PKGSCHEMA, PKGVERSION, UNIQUE_ID, BOUNDBY, REOPTVAR,
TOTAL_SECT, CREATE_TIME, EXPLICIT_BIND_TIME, LAST_BIND_TIME,
VALID, FORMAT, ISOLATION, BLOCKING
FROM SYSCAT.PACKAGES
WHERE PKGNAME = 'P5582207';
db2 rebind P5582207 reopt once
Another way to rebind the package is to use SYSPROC.REBIND_ROUTINE_PACKAGE
However, the SYSPROC.REBIND_ROUTINE_PACKAGE will need to use the routine name ( store procedure name ).
To Find the routine name from package name we have to join through the SYSCAT.ROUTINEDEP
SELECT DISTINCT A.ROUTINENAME, B.BNAME FROM SYSCAT.ROUTINES A,
SYSCAT.ROUTINEDEP B, SYSCAT.PACKAGES C WHERE
B.ROUTINENAME = A.SPECIFICNAME AND B.BNAME = C.PKGNAME AND C.PKGNAME = 'P5582207'
CALL SYSPROC.REBIND_ROUTINE_PACKAGE (
'P','BONUS_INCREASE','RESOLVE ANY REOPT ONCE APREUSE YES')
Below command will rebind the invalid package only.
db2rbind sample -l /tmp/rebindall.log
use all parameter would bind all packages.
db2rbind sample -l /tmp/rebindall.log all
Generate the report for what is in the package cache
db2 "call monreport.pkgcache"
-- Last 30 mins
db2 “call monreport.pkgcache(30)”
syscat.routines has all the sp information including its contents.
List all store procedure in the database
select routineschema, routinename,specificname from syscat.routines where routinetype ='P'
The text column has the content of the store procedure.
PORCEDURES view list all the store procedures in the database.
SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE='PROCEDURE';
SELECT * FROM DBA_PROCEDURES WHERE OBJECT_TYPE='PROCEDURE';
SELECT * FROM ALL_PROCEDURES WHERE OBJECT_TYPE='PROCEDURE';
SOURCE view has the content of the store procedure.
SELECT * FROM USER_SOURCE WHERE NAME = 'STORED_PROC';
SELECT * FROM DBA_SOURCE WHERE NAME = 'STORED_PROC';
SELECT * FROM ALL_SOURCE WHERE NAME = 'STORED_PROC';
sys.procedures in each database has all the sp information in this db. To get all SP in all dbs, we can use sp_MSForEachDB .
EXEC sp_MSForEachDB 'SELECT name FROM sys.procedures'
Sys.objects also has the similar information.
select name from sys.objects where type='P';
To get detail of the sp, we can use sp_helptext
-- sp_helptext SPNAME
sp_helptext FindItemsByDataSet
db2 describe table T2
desc user_tables
sp_help objectname
DGCC: Does not appear in the system catalog, which makes them not persistent for use by other application/session. The table would disappear after session is terminate.
CGTT: Does exist in the system catalog. Can share by other application.
Both temporary table are created in the USER temporary table space. NOT in SYSTEM temporary table space.
To refer the DGTT, we must use SESSION as qualify schema name.
db2 "declare global temporary table DGTT_DEMO1( c1 integer, c2 varchar(20) ) on commit preserve rows"
db2 "insert into SESSION.DGTT_DEMO1 values (1,'row1')"
db2 "insert into SESSION.DGTT_DEMO1 values (2,'row2')"
db2 "select * from SESSION.DGTT_DEMO1"
db2 connect reset
db2 connect to sample
db2 "select * from SESSION.DGTT_DEMO1"
If we reset the connection, the table would not be present and get the SQL0204 error when we try to use it.
The CGTT exist in the system catalog with the type ‘G’. The table would be preserve but the data would be gone after session reset.
db2 "create global temporary table CGTT_DEMO1( c1 integer, c2 varchar(20) ) on commit preserve rows"
db2 "insert into CGTT_DEMO1 values (1,'row1')"
db2 "insert into CGTT_DEMO1 values (2,'row2')"
db2 "select * from CGTT_DEMO1"
db2 connect reset
db2 connect to sample
db2 "select * from CGTT_DEMO1"
db2 "select tabschema,tabname ,type from syscat.tables where tabschema=current schema and tabname = 'CGTT_DEMO1'"
http://www.ibm.com/developerworks/data/library/techarticle/dm-0912globaltemptable/
“NOT LOGGED INITIALLY” is quite interesting feature in DB2. Essentially, it change the behavior of logging for the table.
By default, all the changes will be log within the transaction, however when turn on this feature on the table level. INSERT, DELETE, UPDATE, CREATE INDEX, DROP INDEX, or ALTER TABLE operation on the table would not be log in the same unit of the work. The primary usage for this is when the T1 table is staging area and can be easily created from other source, therefore to avoid the overhead of the logging during the creation time, we can turn off the logging for the table.
To turn on this feature:
CREATE TABLE T1(C1 INT,C_DESC CHAR(20)) NOT LOGGED INITIALLY;
or
ALTER TABLE T1 ACTIVATE NOT LOGGED INITIALLY;
In Oracle, the alternative is use /*+ APPEND */
INSERT /*+ APPEND */ INTO t select c2,desc2 from t2;
But this would only affect the insert statement and the table would not be accessible until commit.
To truncate the table in db2:
ALTER TABLE t1 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
In other DBMS: Sybase, Oracle and SQL server.
truncate table t1;
Windows use NTLM authentication for very long time. FreeTDS is very popular open source database driver. Recently, I spend quite some time to trouble shooting how to use FreeTDS connect to SQL Server which has NTLMv2 enable.
The NTLM setting can be configure on both Server side and client side. Here are the steps you can check and verify the NTLM setting.
gpedit.msc -> Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> Security Options –> Network Security: LAN Manager Authentication Level.
reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa /v LmCompatibilityLevel
When set as “Send NTLMv2 response only. Refuse LM&NTLM”, the value reflect on the register key is 5.
Here list all the incompatibility :
Essentially, if the value is set to 5. the Server would only accept the NTLMv2.
The older version of the freeTDS (0.8) does not support the NTLMv2 and it require the configuration in the newer release ( 0.9.)
Here is the sample of freetds.conf
[DB SERVER NAME]
host = 127.0.0.1
port = 8001
use ntlmv2 = yes
The “use ntlmv2=yes” keyword is required for FreeTDS to use NTLMv2. If you use the older version of the FreeTDS, it would simply ignore this value. If the OS does not use NTLMv2, it will fail back to use NTLM. Therefore I see no downside to set it up in the freetds.conf.
If the OS is set to NTLMv2 (5) but there is no NTLMv2 support turn on on the FreeTDS, we would get the error message like below :
Msg 18452 (severity 14, state 1) from DEPOTSQL Line 1: "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
The DB2_COMPATIBILITY_VECTOR registry variable is used to enable one or more DB2® compatibility features introduced since DB2 Version 9.5.
This register key is to enable the DB2 SQL compatibility to other product.
There are 3 major product are supported:
We can use db2set to change it and this changes require bounce the db2 instance in order to take effective. The value can be set either by name or the hex value.
Below example is to set the DB2 compatible mode to Oracle , so we can use dual instead of SYSIBM.SYSDUMMY1.
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop force
db2start
However, the ORA value is not valid in the DB2 Express-C version, if we try to set it to ORA, we would get the DBI1301E Invalid value error.
The work around is to set by individual features, for example, to enable dual dummy table, we have to set it as 2. Hex value ( 10FFF) is still not supported in Express-C.
SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates
