Archive for arumansoft.com

Bulk Loading of Data – Oracle

Posted in Uncategorized with tags on August 1, 2011 by DBAWork

The ORACLE SQL* Loader is a facility that allows you to populate database tables from flat files. To use this facility, you need to prepare (i) your flat file(s), and (ii) a control file that tells ORACLE how to “map” fields of the flat file to columns of an RDBMS table. Furthermore, the data can also be appended at the end of the control file, making the process much simpler. Most probably, you will prepare one control file for each table that you want to bulk load to. Notice that preparing the data is part of your assignment. There are no “tools” for this. You either create them from some C program (example) that generates the data and outputs it into a file, or if you are lucky, you might find a web page that already has the data that you need with minimal text processing

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.

SQL*Loader (sqlldr) is the utility to use for high performance data loads. The data can be loaded from any text file and inserted into the database.

A simple control file has the following form:

LOAD DATA
INFILE <dataFile>
APPEND INTO TABLE <tableName>
FIELDS TERMINATED BY '<separator>'
(<list of all attribute names to load>)
  • <dataFile> is the name of the data file. If you did not give a file name extension for <dataFile>, Oracle will assume the default extension “.dat“. Therefore, it is a good idea to name every data file with an extension, and specify the complete file name with the extension.
  • <tableName>is the name of the table to which data will be loaded. Of course, it should have been created already before the bulk load operation.
  • The optional keyword APPEND says that data will be appended to <tableName>. If APPENDis omitted, the table must be empty before the bulk load operation or else an error will occur.
  • <separator>specifies the field separator for your data file. This can be any string. It is a good idea to use a string that you know will never appear in the data, so the separator will not be confused with data fields.
  • Finally, list the names of attributes of <tableName> that are set by your data file, separated by commas and enclosed in parentheses. This list need not be the complete list of attributes in the actual schema of the table, nor must it be arranged in the same order as the attributes when the table was created — sqlldr will match attributes to by their names in the table schema. Any attributes unspecified in the list of attributes will be set to NULL.

As a concrete example, here are the contents of a control file test.ctl:

LOAD DATA INFILE test.dat INTO TABLE DBA_TEST FIELDS TERMINATED BY '|' (i, s)

Each line in the data file specifies one tuple to be loaded into <tableName>. It lists, in order, values for the attributes in the list specified in the control file, separated by <separator>. As a concrete example, test.datmight look like:

1|foo
2|bar
3| baz

Recall that the attribute list of test specified in test.ctl is (i, s), where i has the type int, and s has the type char(10). As the result of loading test.dat, the following tuples are inserted into test:

(1, 'foo')
(2, 'bar')
(3, ' baz')

Some Notes of Warning

  • Note that the third line of test.dat has a blank after “|“. This blank is not ignored by the loader. The value to be loaded for attribute s is ' baz', a four-character string with a leading blank. It is a common mistake to assume that 'baz', a three-character string with no leading blank, will be loaded instead. This can lead to some very frustrating problems that you will not notice until you try to query your loaded data, because ' baz' and 'baz'are different strings.
  • Oracle literally considers every single line to be one tuple, even an empty line! When it tries to load data from an empty line, however, an error would occur and the tuple will be rejected. Some text editors love to add multiple newlines to the end of a file; if you see any strange errors in your .log file about tuples with all NULLcolumns, this may be the cause. It shouldn’t affect other tuples loaded.
  • If you are using a Microsoft text editor, such as MSWord, you will find that Bill Gates believes in ending lines with the sequence <CR> (carriage return) <LF> (line feed). The UNIX world uses only <LF>, so each <CR> becomes ^M, the last character of strings in your load file. That makes it impossible for you ever to match a stored string in an SQL query. Here’s how you remove ^M symbols from your file. Let’s say the file with ^M symbols is bad_myRel.dat. Then the following command will create myRel.dat without ^M symbols:
                 cat bad_myRel.dat | tr -d '15' > myRel.dat

    If you’re an emacs fan, type in the following sequence to modify your current buffer:

    ESC-x replace-string CTRL-q CTRL-m ENTER ENTER
  • The Oracle bulk loader is called sqlldr. It is a UNIX-level command, i.e., it should be issued directly from your UNIX shell, rather than within sqlplus. A bulk load command has the following form:
    sqlldr <yourName> control=<ctlFile> log=<logFile> bad=<badFile>
    Everything but sqlldr is optional -- you will be prompted for your username, password, and control file. <ctlFile> is the name of the control file. If no file name extension is provided, sqlldr will assume the default extension ".ctl". The name of the data file is not needed on the command line because it is specified within the control file. You may designate <logFile> as the log file. If no file name extension is provided, ".log" will be assumed. sqlldr will fill the log file with relevant information about the bulk load operation, such as the number of tuples loaded, and a description of errors that may have occurred. Finally, you may designate <badFile> as the file where bad tuples (any tuples for which an error occurs on an attempt to load them) are recorded (if they occur). Again, if no file extension is specified, Oracle uses ".bad". If no log file or bad file are specified, sqlldr will use the name of the control file with the .log and .badextensions, respectively.

    As a concrete example, if sally wishes to run the control file test.ctl and have the log output stored in test.log, then she should type

    sqlldr sally control=test.ctl log=test.log
  • It is possible to use just the control file to load data, instead of using a separate data file. Here is an example:
    LOAD DATA INFILE * INTO TABLE DBA_TEST FIELDS TERMINATED BY '|' (i, s) BEGINDATA 1|foo 2|bar 3| baz
    The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file

DBAWorkDatabase support, Database services, Oracle ADF

Advertisements

SQL5005C error IBM DB2 LUW

Posted in DB2 with tags , , , , , , , on July 16, 2011 by DBAWork

Product : IBM DB2 LUW
Product version : 9.1,9.5,9.7
Platform: Linux ,Unix and Windows

Title : SQL5005C error

Question :
SQL5005C error window pop up.
Cause :

1. DB2SYSTM not found in installation directory.

Following entries are seen in db2diag file.

2010-12-03-03.06.32.584000+540 I22054739H284 LEVEL: Error
PID : 5352 TID : 5376 PROC : db2fmp.exe
INSTANCE: DB2 NODE : 000
EDUID : 5376
FUNCTION: DB2 UDB, config/install, sqlfGetDbmCfg, probe:10
RETCODE : ZRC=0xFFFFEC73=-5005
2010-12-03-03.06.32.584000+540 I22055025H339 LEVEL: Severe
PID : 5352 TID : 5376 PROC : db2fmp.exe
INSTANCE: DB2 NODE : 000
EDUID : 5376
FUNCTION: DB2 UDB, config/install, sqlf_alert, probe:8
DATA #1 : Hexdump, 4 bytes
0x05CAE74C : 73EC FFFF s…
2. File permission changed from 755 to 775,700 and 744 because of rmsoke

Following entries are seen in db2diag

2011-05-27-13.04.18.619309-240 I95211A453 LEVEL: Error
PID : 847920 TID : 1 PROC : db2agent
(instance) 0
INSTANCE: qasa12 NODE : 000
APPHDL : 0-24 APPID: *LOCAL.qasa12.110527170418
FUNCTION: DB2 UDB, config/install, sqlf_read_db_and_verify, probe:30
MESSAGE : SQL5005: sqlf_openfile rc =
DATA #1 : Hexdump, 4 bytes
0x0FFFFFFFFFFE9820 : 840F 0001 ….

2011-05-27-13.04.18.619937-240 I95665A348 LEVEL: Error
PID : 847920 TID : 1 PROC : db2agent
(instance) 0
INSTANCE: qasa12 NODE : 000
APPHDL : 0-24 APPID: *LOCAL.qasa12.110527170418
FUNCTION: DB2 UDB, config/install, sqlf_read_db_and_verify, probe:0
RETCODE : ZRC=0xFFFFEC73=-5005

2011-05-27-13.04.18.620423-240 I96014A420 LEVEL: Severe
PID : 847920 TID : 1 PROC : db2agent
(instance) 0
INSTANCE: qasa12 NODE : 000
APPHDL : 0-24 APPID: *LOCAL.qasa12.110527170418
FUNCTION: DB2 UDB, config/install, sqlf_alert, probe:9
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0x0FFFFFFFFFFE99F0 : FFFF EC73 …s

2011-05-27-13.04.18.620591-240 I96435A315 LEVEL: Severe
PID : 847920 TID : 1 PROC : db2agent
(instance) 0
INSTANCE: qasa12 NODE : 000
APPHDL : 0-24 APPID: *LOCAL.qasa12.110527170418
FUNCTION: , , , probe:9
RETCODE : ZRC=0xFFFFEC73=-5005

Trace shows following entries

db2trace file showS:

2466 | | | | | | | | | | | | | | | | sqlf_read_db_and_verify data
[probe 0]
2467 | | | | | | | | | | | | | | | | | | | | sqloclose entry
2468 | | | | | | | | | | | | | | | | | | | | sqloclose exit
2469 | | | | | | | | | | | | | | | | | | | _pdlogInt exit
2470 | | | | | | | | | | | | | | | | | | pdLogRC exit
2471 | | | | | | | | | | | | | | | | | sqlt_logerr_zrc exit
2472 | | | | | | | | | | | | | | | | sqlf_read_db_and_verify exit
[rc = 0xFFFFEC73 = -5005]

3. Open control center in Windows 2003 server gets SQL5005C error

The user needs to have read,write and execute access on db2 directory.

More On DB2 support, DBAWorkDatabase support, Database services, Oracle ADF

Oracle RAC

Posted in Uncategorized with tags on July 2, 2011 by DBAWork

In database computing, Oracle Real Application Clusters (RAC) — an option for the Oracle Database software produced by Oracle Corporation and introduced in 2001 with Oracle9i — provides software for clustering and high availability in Oracle database environments. Oracle Corporation includes RAC with the Standard Edition of Oracle Database (aka Baby RAC), but makes it an extra-charge option for the Enterprise Edition.

Oracle RAC allows multiple computers to run Oracle RDBMS software simultaneously while accessing a single database, thus providing a clustered database.

In a non-RAC Oracle database, a single instance accesses a single database. The database consists of a collection of data files, control files, and redo logs located on disk. The instance comprises the collection of Oracle-related memory and operating system processes that run on a computer system.

In an Oracle RAC environment, two or more computers (each with an instance) concurrently access a single database. This allows an application or user to connect to either computer and have access to a single coordinated set of data.

On 10R2 if the Oracle binaries are installed with the RAC, one can’t use those binaries to start Oracle Instance if CRS is not running, so in case when one needs to start the instance without CRS one needs to rebuild the oracle and it’s libraries to start the instance.

To turn off RAC
# link the oracle libraries
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_off
# rebuild oracle
$ cd $ORACLE_HOME/bin
$ relink oracle

To turn on RAC
# link the oracle libraries
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
# rebuild oracle
$ cd $ORACLE_HOME/bin
$ relink oracle

DBAWorkDatabase support, Database services, Oracle ADF

DB2PD – Diagnosing an Application Information

Posted in DB2 with tags on July 1, 2011 by DBAWork

As per the memory structure DB2 has setting for minimum connection pool to be maintained on database level. Following is the command to get more information for the applications which are connected to current database.

Using the -apinfo parameter to capture detailed runtime information about the lock owner and the lock waiter

The following sample output was generated under the same conditions as those for Example
 
venus@boson:/home/venus=>db2pd -apinfo 47 -db pdtestDatabase Partition 0 — Database PDTEST — Active — Up 0 days 00:01:30Application :
Address :                0x0780000001676480
AppHandl [nod-index] :   47       [000-00047]
Application PID :        876558
Application Node Name :  boson
IP Address:              n/a
Connection Start Time :  (1197063450)Fri Dec  7 16:37:30 2007
Client User ID :         venus
System Auth ID :         VENUS
Coordinator EDU ID :     5160
Coordinator Partition :  0
Number of Agents :       1
Locks timeout value :    4294967294 seconds
Locks Escalation :       No
Workload ID :            1
Workload Occurrence ID : 2
Trusted Context :        n/a
Connection Trust Type :  non trusted
Role Inherited :         n/a
Application Status :     UOW-Waiting
Application Name :       db2bp
Application ID :         *LOCAL.venus.071207213730  ClientUserID :           n/a
ClientWrkstnName :       n/a
ClientApplName :         n/a
ClientAccntng :          n/aList of inactive statements of current UOW :
UOW-ID :          2
Activity ID :     1
Package Schema :  NULLID
Package Name :    SQLC2G13
Package Version :
Section Number :  203
SQL Type :        Dynamic
Isolation :       CS
Statement Type :  DML, Insert/Update/Delete
Statement :       insert into pdtest values 99venus@boson:/home/venus =>db2pd -apinfo 46 -db pdtest

Database Partition 0 — Database PDTEST — Active — Up 0 days 00:01:39

Application :
Address :                0x0780000000D77A60
AppHandl [nod-index] :   46       [000-00046]
Application PID :        881102
Application Node Name :  boson
IP Address:              n/a
Connection Start Time :  (1197063418)Fri Dec  7 16:36:58 2007
Client User ID :         venus
System Auth ID :         VENUS
Coordinator EDU ID :     5913
Coordinator Partition :  0
Number of Agents :       1
Locks timeout value :    4294967294 seconds
Locks Escalation :       No
Workload ID :            1
Workload Occurrence ID : 1
Trusted Context :        n/a
Connection Trust Type :  non trusted
Role Inherited :         n/a
Application Status :     Lock-wait
Application Name :       db2bp
Application ID :         *LOCAL.venus.071207213658

ClientUserID :           n/a
ClientWrkstnName :       n/a
ClientApplName :         n/a
ClientAccntng :          n/a

List of active statements :
*UOW-ID :          3
Activity ID :     1
Package Schema :  NULLID
Package Name :    SQLC2G13
Package Version :
Section Number :  201
SQL Type :        Dynamic
Isolation :       CS
Statement Type :  DML, Select (blockable)
Statement :       select * from pdtest

More on Database Support, DBAWorkDatabase support, Database services, Oracle ADF

System Databases – SQL Server

Posted in DB2, General, Oracle, SQL Server with tags on June 3, 2011 by DBAWork

Microsoft SQL Server is a relational database server developed by Microsoft : It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). There are at least a dozen different editions of Microsoft SQL Server aimed at different audiences and for different workloads (ranging from small applications that store and retrieve data on the same computer, to millions of users and computers that access huge amounts of data from the Internet at the same time).

Master DB

Records all the system-level information for an instance of SQL Server.
This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.
Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.
Therefore, SQL Server cannot start if the master database is unavailable.

msdb DB

It is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.

model DB

Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

ResourceDB

Is a read-only database that contains system objects that are included with SQL Server. SQL Server system objects, such as sys.objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

Tempdb DB

It is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

  • Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
  • Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
  • Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
  • Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

Distribution

Database responsible for the replication metadata between the publisher and subscriber(s)

ReportServer

Primary database for Reporting Services to store the metadata and object definitions

  • Reports security
  • Job schedules and running jobs
  • Report notifications
  • Report execution history

ReportServer TempDB

Temporary storage for Reporting Services

Session information

Cache

Remote database support, DBAWorkDatabase support, Database services, Oracle ADF