HandyDBA

about

2012-04-20T16:21:59+01:00

HandyDBA The HandyDBA is Alan Jeskins-Powell. Over 20 years of database administration experience covering Oracle, SQL Server, DB2, MySQL and PostgreSQL. Oracle versions up to 11g, includes RAC, Dataguard and RMAN, databases from a few GB to 8TB. SQL Server versions from 2000 to 2008 R2. Postgres versions up to 8.3 DB2 versions 9.1 and 9.7.2

blog

2013-06-17T09:54:26+01:00

2013-06-17 SSH Login Banners There are two places where login banners can be configured. What might be less well known are techniques for disabling their display when logging in via SSH ...read 2013-06-03 MySQL In my spare time I do some work for a local charity - which has meant getting up to speed with some new (for me) web and database software. ...read

cv

2011-05-16T16:14:11+01:00

Experience Summary Operating Systems * AIX (5.3) * Sun Solaris 6, 8, 9, 10 * Tru64 4.0, 5.1 (HP/Compaq Unix) * HP/UX 10.2, 11i * Linux (Many releases, predominantly Red Hat, Suse, Fedora and Oracle Enterprise) * Microsoft Windows 2003, 2008 * VMWare server

db2autodelrec

2012-04-13T12:29:06+01:00

9.7 Database Configuration Parameters When a FULL backup is run DB2 can delete the oldest history records and archive log files. This is governed by 3 database parameters, e.g. for database ED1 $ db2 get db cfg for ed1 ... Number of database backups to retain (NUM_DB_BACKUPS) = 12 Recovery history retention (days) (REC_HIS_RETENTN) = 60 Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF ...

db2links

2011-05-25T14:55:49+01:00

Note: I try to check the links periodically to make sure they all work but YMMV. I am also not responsible for the content of external sites. link DB2 isolation levels - pay particular attention to UR which is used a lot by SAP link DB2 SAP automatic database maintenance Some good explanations of basic DB2 automation, e.g. STMM, auto. storage, runstats. link DB2 Basics: Fun with Dates and Times link IBM Data Studio features, includes links for downloads. link Registry and environment v…

db2maintwindow

2013-02-15T10:32:34+01:00

With particular reference to automatic jobs, e.g. collecting runstats. References: * Maintenance windows - short, to the point, doesn't tell you much. * Sample XML - just tells you where to find the sample XML on the server ... * AUTOMAINT_SET_POLICYFILE procedure can fail with a misleading error message * Automate DB2 9.7 database maintenance - DeveloperWorks article - fairly useful.

db2notes

2013-02-15T10:34:27+01:00

db2dsdriver.cfg - DB2 equivalent to Oracle tnsnames.ora (or as close as DB2 gets anyway). Automatic deletion of recovery objects db2pd (monitor and troubleshoot) Backup to TSM Maintenance Windows

db2pd

2013-02-15T10:56:00+01:00

IBM: db2pd - Monitor and troubleshoot DB2 database command Table usage statistics Usage statistics Database partition uptime Returns the state and uptime of all database partitions. $ db2pd - -alldbpartitionnums Database Partition 0 -- Active -- Up 57 days 03:58:06 -- Date 06/14/2011 07:27:57

db2snippets

2012-04-13T12:29:08+01:00

db2testconn

2012-04-13T12:29:08+01:00

Test database connectivity in a SAP environment. SAP provide a utility to perform just this check, e.g. [db2]: /home/db2/bin $ R3trans -d -v -x This is R3trans version 6.14 (release 701 - 26.01.09 - 12:46:00). unicode enabled version R3trans finished (0000). [db2]: /home/db2/bin $ echo $? 0

db2tsm

2011-06-21T10:10:53+01:00

How to configure DB2 for backup to TSM. Possible errors Before you start * The TSM bit level must match the DB2 bit level but it need not match the O/S. * The db2adutl and dsmapipw utilities read their environment from userprofile every time they are run but db2 only reads it on instance start. DB2 must be restarted for changes in userprofile to take effect. * Note that a SAP install will assign the DB2 instance owner the C shell. This changes the way DSMI variables are set.

db2tsmprob

2011-06-23T08:42:20+01:00

Configuration Environment variables Backup to TSM requires 3 environment variables. If one or more of these is not set, or set incorrectly you may see TSM Reason code 106. In particular note: * For online backups; the DB2 instance must be restarted if DSMI environment variables are changed. * If DB2 is started from SAP (by running startsap) be aware that the SAP admin user (adm) does not run userprofile so DSMI variables must be sourced before startsap is run.

dbmscmd

2011-01-26T12:21:53+01:00

Get current database name DB2 SQL> select CURRENT SERVER as DBNAME from SYSIBM.SYSDUMMY1 DBNAME ED1 Oracle SQL> select NAME as DBNAME from V$DATABASE; DBNAME --------- TEST SQL Server 1> select DB_NAME() as DBNAME 2> go DBNAME ------------------------- master

development

2011-06-23T09:48:40+01:00

As a general rule, my preferred date format is now YYYY-MM-DD HH:MI:SS This format works (with only a small caveat) in all of the DBMS I'm currently using, Oracle, SQL Server and DB2. The caveat is for Oracle which requires an alter session to work, easily done in a login script. PS: By 'works' I mean that date/time functions in each of the DBMS can parse this date format without the use of any conversion functions, e.g. convert (SQL Server), to_date (Oracle).

legal

2010-12-31T17:30:10+01:00

Unless otherwise noted all content on this site is Copyright Alan-Jeskins-Powell. Various companies or entities (including Oracle Corporation, PostgreSQL, etc.) and the products and/or services they supply are mentioned on this site. Trademarks in their products and/or services are held by the companies mentioned.

links

2011-06-01T14:43:15+01:00

People / Organisations Jonathan Lewis Blog Web Oracle internals Pythian Blog Web Remote DBA services. In depth articles. Tim Hall Blog Web For most things Oracle, always a good read link The best DBA's automate everything [ John Sansom ] - how true! link The best DBA's document everything [ John Sansom ] - equally true! Reference DB2

mysqllinks

2011-05-17T15:46:22+01:00

Note: I try to check the links periodically to make sure they all work but YMMV. I am also not responsible for the content of external sites. link Howto: Backup restore link MySQL Workbench

mysqlnotes

2011-01-06T09:10:23+01:00

mysqlsnippets

2011-05-17T15:59:01+01:00

List databases on server mysql> show databases; +------------------------+ | Database | +------------------------+ | mysql | | test | +------------------------+ 2 rows in set (0.11 sec) Get database size SELECT table_schema "Data Base Name" ,sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" ,sum( data_free ) / 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema ; -- On 5.1 (informati…

oi

2012-05-04T15:13:25+01:00

Web :: an enthusiastic austrian solaris user OpenIndiana (OI) is a fork of OpenSolaris. Since the acquisition of Sun by Oracle OpenSolaris has become somewhat less open. These are notes relating to my install of OI. Some of this is based on work by others - their work is appreciated and noted.

opsys

2011-10-04T15:15:55+01:00

Linux / Other Unix Notes alias mount sudo Shell script OpenIndiana Windows Notes :: Links Remote Desktop Connection Manager NAS Not strictly an operating system but I just needed somwhere to keep notes about NAS. Looking into NAS for home/small office use. There are several (that I know of) open-source or community NAS distributions

oraasm

2011-01-07T15:04:18+01:00

Using loopback devices in place of real disks Refs: Jeff Hunter, How to use Files in place of Real Disk Devices for ASM Fabien Faye, Howto create Loop Device on REDHAT, CENTOS or FEDORA For testing only Create disk files $ cd /exports/oracle $ mkdir asmdisks $ chown oracle:dba asmdisks $ dd if=/dev/zero of=asmdisks/_file_disk10 bs=1k count=1000000 # 1000MB $ dd if=/dev/zero of=asmdisks/_file_disk11 bs=1k count=1000000 # 1000MB $ dd if=/dev/zero of=asmdisks/_file_disk12 bs=1k count=10000…

oraerrors

2011-01-07T16:18:26+01:00

ORA-38029 “Object statistics are locked” You might also see this reported from DBMS_STATS as ORA-20005 “object statistics are locked” See Metalink note 433240.1 Possible causes: * DBMS_STATS.LOCK_[SCHEMA|TABLE]_STATS has been run * Import (imp rows=N) or Datapump (impdp content=metadata_only) * Statistics on queue tables are designed to be empty and locked.

oralinks

2011-05-19T08:31:16+01:00

Note: I try to check the links periodically to make sure they all work but YMMV. I am also not responsible for the content of external sites. link Accessing Automatic Storage Management Files with the XML DB Virtual Folder [ Oracle ] link Support contact telephone numbers link Oracle link Technet link My Oracle Support (MOS) - Flash version link My Oracle Support (MOS) - HTML version

oranotes

2011-01-31T12:12:42+01:00

User authentication If you use Windows domains then this would be how to go about enabling O/S authentication. Do this at your own risk 1. Create a password file (if one does not already exist), e.g. orapwd file=$ORACLE_HOME\database\orapwSID.ora password=****

orasnippets

2012-04-13T12:29:10+01:00

Useful(?) bits of SQL, PLSQL. Date/time Timezone conversion Convert date/time from one timezone to another select to_date('15-Aug-2008 10:00:00','dd-Mon-yyyy hh24:mi:ss') as EST, NEW_TIME(to_date('15-Aug-2008 10:00:00','dd-Mon-yyyy hh24:mi:ss'),'EST','GMT') as GMT from dual;

pshlinks

2011-05-17T09:17:42+01:00

Note: I try to check the links periodically to make sure they all work but YMMV. I am also not responsible for the content of external sites. link Using Access Database Engine drivers to read text files [ Chad Miller ] link Using ADE drivers to read Access and Excel [ ScriptingGuy ] link Using the SQL Server provider [ MSDN ] link Powershell - Step By Step - Book notes [ The Network Guru ] link Reading share security [ Chris Dent ] link List software installed on PC [ Ying Li ] link…

rdbms

2011-05-17T15:14:15+01:00

Equivalent commands See Commands Oracle Notes :: Snippets :: Links Documentation: 10.1 :: 10.2 :: 11.2 ASM :: Errors SQL Server Notes :: Snippets :: Links Documentation: 2000 :: 2005 :: 2008 :: 2008 SSMS Express Linked servers :: Trace/Profiler :: xp_cmdshell

remoteapp

2012-05-03T12:35:40+01:00

The setup below was designed and implemented to run SSMS on client PC's with limited local resources (e.g. memory). References: RemoteApp Remote Desktop Services RemoteApp setup RemoteApp setup RemoteApp Tool Run remote app from web page Also by Kim Knight

saposcol

2013-03-12T10:37:36+01:00

saposcol is one of at least two components necessary for collecting SAP performance statistics; e.g. those that can be viewed in ST03G and STAD. On at least two occasions saposcol has failed to start with a shared memory error, e.g. 49>saposcol -l -l 'Can't create shared memory'

sapunlockuser

2011-06-23T08:53:50+01:00

Unlock SAP user If you try SAP logon and you get “account locked due to incorrect logins” error you can use one of the following methods to unlock the user: Transaction Use RSUSR200 to list locked users, then SU01 to unlock the user. Database update This is not recommended by SAP - use at your own risk It is useful though if the locked user happens to be DDIC ...

sapworkload

2011-03-01T16:01:21+01:00

When SAP is installed the 'SAP' workload will be created. Documentation for some of these variables is ... sparse. Where '????' appears I haven't so far been able to find any documentation on the parameter and/or the value it is being set to. -- Request either reduced optimization features or rigid use of optimization features at the specified optimization level DB2_REDUCED_OPTIMIZATION=4,INDEX,JOIN,NO_TQ_FACT,NO_HSJN_BUILD_FACT,STARJN_CARD_SKEW,NO_SORT_MGJOIN,CART OFF,CAP OFF -- List prefe…

shellscript

2011-10-04T15:29:52+01:00

Variable scope Variable scope becomes important particularly when processing loops. Consider this example: typeset -i counter=0 find ${backupPath} -name backup-archive-\*.bak.gz | while read backupFile do echo "... removing $backupFile" let counter=counter+1 done echo "Processed $counter files"

sidebar

2017-05-22T12:17:19+01:00

Home Blog About Database administration Operating systems Development Links ---------- Following require login: ScratchPad ---------- ---------- ----------

sqlcmdshell

2012-04-13T12:29:10+01:00

Creates a process and runs command with same permissions as user running SQL Server service. Disabled by default in 2005 and 2008. Can be enabled by running: -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO

sqldbcc

2011-01-27T08:04:31+01:00

ShrinkFile # Shrinkfile to specified size in megabytes # if size not specified will try to shrink file to default file size 1> use tempdb 2> go 1> dbcc shrinkfile( tempdev, 10240 ) 2> go DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages 2 1 1310720 278920 120 120 (1 rows affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator.

sqlite

2011-05-17T15:57:48+01:00

SQLite System.Data.SQLite Note that the sqlite3 executable cannot access encrypted databases - that facility is a cost option. Details of System.Data.SQLite can be found here Note that this .NET assembly can access encrypted SQLite databases. Copy data from one database to another This is useful if you are using Unix, on Windows try using Cygwin.

sqllinkedservers

2012-04-13T12:29:10+01:00

Excellent article from Linchi Shea on performance issues using linked servers. To summarise his article: For the local optimiser to have access to remote statistics the connection to linked server must be at least one of: table owner, sysadmin, db_owner or db_ddladmin. Without these statistics query optimiser may well decide to scan the remote table and copy data to local for evaluation.

sqllinks

2012-04-20T16:10:28+01:00

Note: I try to check the links periodically to make sure they all work but YMMV. I am also not responsible for the content of external sites. link Fundamentals of storage systems, latency and SQL Server [ SQLServerPedia ] Original article by Wes Brown link Database documentation using extended properties. Note: Requires free registration at [ SQLServerCentral ] link Simple recommendation for instance memory settings [ Thomas LaRock ] link Explanation of SQLServer 2000 sp4 self-locking […

sqlnotes

2011-01-07T10:39:46+01:00

Support notes Support notes that I have used, or found to be useful. DocID Abstract 308518 Refresh of Current Activity in SEM May Cause “Error 1222: Lock Request time out period exceeded” Error Message 309256 How to troubleshoot SQL Server error 8645, “A time out occurred while waiting for memory resources to execute the query” 316749 There may not be enough virtual memory when you have a large number of databases in SQL Server 906344The blocked column in the sysprocesses table is populated…

sqlsnippets

2011-02-02T14:37:58+01:00

DBCC

sqltrace

2011-06-08T09:19:40+01:00

Normally use SQL Profiler GUI, but it's a bit of a pain. Some links about scheduling tracing in SQL Server Of these, the first is straightforward and the last is a fairly comprehensive wrapper around the trace functions and includes data for all the event and column codes.…

start

2017-05-22T12:22:26+01:00

About The HandyDBA Update May 2017 It's been a long time since this site was updated. My interests and work have moved away from databases into server and web technologies. I'll keep this site alive in the hope that some of the material may prove useful. Alan, See What Did I Learn Today for more up to date articles.

unixaliases

2012-04-13T12:29:12+01:00

Vaguely related to the reason we are using sudo, this is a short note about aliases. We have ours defined in a setup script called from .bash_profile and by every script, e.g. # db2ckbkp needs read access to backup files alias db2ckbkp='sudo -i -u ${instanceName} db2ckbkp' # db2diag needs write access to db2dump alias db2diag='sudo -i -u ${instanceName} db2diag'

unixmount

2011-07-29T15:25:43+01:00

In the process of bulding servers for what will be a large-ish SAP landscape. Some of the directory structure is dictated by SAP, some by DB2 and some by us. On some of our servers it's expedient to have a single large filesystem rather than the filesystem per dirrectory that will appear on the production servers.

unixnotes

2011-09-28T12:11:13+01:00

For a running process There are a couple of alternatives that I know of, both have to be run as root: * Using 'ps' # ps -elf | grep -i "db2sysc" 4 S db2sp1 15455 15453 1 85 0 - 596320 ? Jun01 ? 04:29:32 db2sysc 0 # ps eww 15455 PID TTY STAT TIME COMMAND 15455 ? Sl 269:31 db2sysc 0 INSTHOME=/db2/db2sp1 ...etc

unixsudo

2011-02-01T14:09:59+01:00

sudo man sudo man sudoers We are currently implementing a SAP landscape which will be using a large-ish number of database servers. The SAP installer creates a DB2 instance owner of the form db2. As we are going to have a large number of servers to administer I wanted:

winlinks

2011-05-17T12:55:36+01:00

Note: I try to check the links periodically to make sure they all work but YMMV. I am also not responsible for the content of external sites. link Run remote app. over RDP, works with Win XP client [ Liam Westley ] On Win XP this requires SP3 and RDP v7.0 which can be downloaded from Microsoft link Technet forum thread which includes bullet points for setting up Remote Desktop app over RDS

winnotes

2011-02-04T11:25:37+01:00

RDC Keyboard shortcuts Some keyboard shortcuts when using a Remote Desktop Connection via RDP. Key combination Comment Win equivalentALT+DELETE Display the Windows menu ALT+INSERT Switch programs in most recently used order ALT+TAB ALT+HOME Display the Start menu CTRL+ESC ALT+PAGE DOWN Switch programs from right to left CTRL+PAGE DOWNALT+PAGE UP Sw…

winrdcman

2011-05-17T15:54:40+01:00

Remote Desktop Connection manager is a Microsoft freebie for managing multiple RDP sessions. Download For Windows XP it also needs an RDP update (MS KB969085) (That RDP update also seems to make normal RDP connections using MSTSC a bit more responsive.)