How to use RazorSQL to connect to DB2 from windows

Hi, below are the steps i use to connect to DB2 :
1. RUN ODBC 32bit instead of 64 bits c:\windows\sysWOW64\odbcad32.exe
2.Define new system DSN with below spec.

note :other remain default

3. Install razorSQL and then run the connection wizard as below :

Hope that helps..

TQ

SQL - How to use join

Hi, In Mssql, you may use view to link those wanted tables in view.
Instead of using view,   you may write in SQL


' ------------  inner join-------------------
select  * from tblSalesman s, tbldept d
where s.deptcode  =  d.code
'  in which both table's specified column are matched and display those matched records from both table



'-------------- left outer join -------------------------
select  * from tblSalesman s, tbldept d
where s.deptcode  *= d.code
'  in which both table's specified column are matched and display those matched records from both table plus non-matched records from the left table



'------------ right outer join --------------------------
select  * from tblSalesman s, tbldept d
where s.deptcode  =* d.code

' in which both table's specified column are matched and display those matched records from both table plus non-matched records from the right table


Hope it helps...



TQ

SQL - How to insert new record

Hi, before adding any new master record, why not add this begin transaction 
with this option, you can have option to commit or rollback to last change.

-----------------------------------------

'is to have option for rollback
begin transaction
insert into tblSalesman (code, name, deptcode, branchcode, runningCode, runningNo, deleted, insertedby)
values ('YY', 'Yeong', 'IT', 110,'SA/N/YK', 1, 0, 1);

' is to rollback last change
begin transaction

rollback

;is to confirm commit

begin transaction 

commit

*  once commit, no rollback is applicable

TQ

DB2 - How to delete records in DB2 AS400

Hi, there.

Wondering how can we delete certain records in  IBM emulator Eg, RPG screen or in telnet

You may either use STRDFU or UPDDTA table

Say you are about to use strdfu ,
1. Type strdfu
2. Enter "5"  update using temporary program
3. enter data file "xxxx", library "xxxxx" member "*first"
4 scroll down and search for the required recoard. Until you see the specific record, you may press
"Shift + F11"  twice to delete that record.

SQL - How to retrieve last 100 records ?

Hi, there.
Normally when we do data validation, we need to retrieve some latest records. Let's take last 100 records.

Method 1 by datetime field 

USE [dataDB]

go

select top 100 * from tbltransaction
order by trdate desc, trtime desc

Method 2 by running no ID field (PK)

USE [dataDB]
go

select * from tblTransaction
where ID >
( (select COUNT(*) from tblTransaction) - 150)
order by ID desc

SQL - Truncate , Drop table

1. Hi, don't mess up with these sql command, truncate and drop . Below are the definitions.
2. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove thetable definition in addition to its data, use the DROP TABLE statement.

TRUNCATE TABLE 
    [ { database_name .[ schema_name ] . | schema_name . } ]
    table_name
    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> } 
    [ , ...n ] ) ) ]
[ ; ]

<range> ::=
<partition_number_expression> TO <partition_number_expression>

DROP TABLE [ database_name . [ schema_name ] . | schema_name . ]
table_name [ ,...n ]
 [ ; ]

TQ

Let's understand what it means by these code - ORA - XXXXX

Hi, just for your reference :

ORA-00942 table or view does not exist

TNS-12571 TNS:packet writer failure
Cause: An error occurred during a data send. This message is not normally
visible to the user.
In addition, this message could occur when any of the following SQL*Plus
commands have been issued:
n SHUTDOWN ABORT
n SHUTDOWN IMMEDIATE
n SHUTDOWN TRANSACTIONAL
Action: For further details, turn on tracing and re-execute the operation. If the
error persists, contact Oracle Support Services.

Can delete OEE db 's trace log ?

According to Oracle, you may delete all these TRC, TRM as long as there are no severe error in alert.log You may delete those quite obsolete log..just leave the latest one. W/o these trace log, we can not trace the rootcause..Normally, alertxxx log is the basic log to start trouble-shooting.  




TQ

How to retrieve OEE database log

Hi, wondering how to start trouble-shooting the OEE database which is suspiciously in error ?
Let's say you have checked the application level and could not find any error. Then let 's turn to OS level Eg, event viewer for the log. And then next will be DB log. 1st will be alert_xxx.log which is normally located at db server \app\oracle\diag\rdbms\orcl\orcl\trace.

alert_orcl.log

Thu Jun 26 16:33:29 2014
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 2
Number of processor cores in the system is 2
Number of processor sockets in the system is 1
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.

xxxxxxxxxxxx

xxxxxxxxxxxxxxx

Errors in file F:\APP\diag\rdbms\orcl\orcl\trace\orcl_ora_3628.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'F:\APP\ORADATA\ORCL\REDO01.LOG'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

Clearing online redo logfile 1 F:\APP\ORADATA\ORCL\REDO01.LOG

Clearing online log 1 of thread 1 sequence number 0

Errors in file F:\APP\diag\rdbms\orcl\orcl\trace\orcl_ora_3628.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'F:\APP\ORADATA\ORCL\REDO01.LOG'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

----------------- example of the log ------------------

With this, look up the error code Eg, ORA-00313 and look into \APP\diag\rdbms\orcl\orcl\trace\orcl_ora_3628.trc  for detail. You should be able to find the culprit here.

Hope that helps..

Oracle - how to delete RAC database backup set

Hi, If you used Enterprise manager database console to do the backup and wanted to know how to remove existing backup sets..Pls refer below steps :

1.open sqlplus :  sqlplus sys/password@ORCL as sysdba;
2. Run below script to spool the output into html file

spool full_path_alias_directory.html
-- ASM Versions 10.1, 10.2, 11.1  & 11.2
SET MARKUP HTML ON
set echo on

set pagesize 200

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " "  from dual;


select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';

SELECT CONCAT('+'||GNAME, SYS_CONNECT_BY_PATH(ANAME, '/'))
 FULL_PATH, SYSTEM_CREATED, ALIAS_DIRECTORY, FILE_TYPE
 FROM ( SELECT B.NAME GNAME, A.PARENT_INDEX PINDEX,
 A.NAME ANAME, A.REFERENCE_INDEX RINDEX,
 A.SYSTEM_CREATED, A.ALIAS_DIRECTORY,
 C.TYPE FILE_TYPE
 FROM V$ASM_ALIAS A, V$ASM_DISKGROUP B, V$ASM_FILE C
 WHERE A.GROUP_NUMBER = B.GROUP_NUMBER
 AND A.GROUP_NUMBER = C.GROUP_NUMBER(+)
 AND A.FILE_NUMBER = C.FILE_NUMBER(+)
 AND A.FILE_INCARNATION = C.INCARNATION(+)
 )
 START WITH (MOD(PINDEX, POWER(2, 24))) = 0
 CONNECT BY PRIOR RINDEX = PINDEX;


spool off

exit

--------------------- end of script -----------------------------


3. Once finished, open the html and check the full path of the backup set made
This html is to show all existing backup sets and file type used in ASM storage..
Eg..  

FULL PATH 
+ARCH/ORCL/BACKUPSET/2015_01_11/nnndf0_BACKUP_ORCL_000021_0111150830_0.458.86873 9507
FILE TYPE 
BACKUPSET


4. And then issue this sql as :

alter diskgroup ARCH drop file '+ARCH/ORCL/BACKUPSET/2015_01_11/nnndf0_BACKUP_ORCL_000021_0111150830_0.458.868739507'; 

5. If success, it would show message "Altered "

Hope that helps....

Cheers,

Oracle DB - RMAN command

RMAN is used to backup and restore Oracle database and also will be used in other backup tools like Symantec backup exec , and etc...

To connect from the operating system command line, enter the connection as in the following examples:
# example of operating system authentication
% rman TARGET / NOCATALOG

# example of Oracle Net authentication
% rman TARGET SYS/oracle@dbSID NOCATALOG



With catalog mode :
# example of operating system authentication
% rman TARGET /

# example of Oracle Net authentication
% rman TARGET SYS/oracle@dbSID

* to list out all backup set
LIST BACKUP;



% rman TARGET /
RMAN> BACKUP DATABASE;    # RMAN defaults to NOCATALOG mode
# operating system authentication
% rman TARGET / CATALOG rman/cat@catdb

# Oracle Net authentication
% rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb


note : Operating system authentication takes precedence over password file authentication.


you can refer to the below command for validating the the backup files before retoring them .
 RESTORE DATABASE VALIDATE;
 RESTORE ARCHIVELOG ALL VALIDATE;


How to delete expired archive log ? Archive log is an online redo log. Sometimes it could go wrong
and get expired and required to delete it before backup can take place..

RMAN>list expired archivelog all;
RMAN>crosscheck archivelog all;
RMAN>delete noprompt expired archivelog all;


NOTE : Loosing an archive redolog will compromises the recoverability of the RDBMS. So double check if this archive ever has been backed up via RMAN or normal filesystem backups.
If NO backup is found, than make a FULL database backup IMMEDIATELY !!!!!!



To be continued...

Oracle sqlplus command - output to file

sqlplus /nolog

SQL> spool /tmp/output.html
SQL> @script_name.sql     ;;; the script goes here...
SQL> exit



Hope that helps....

SQL - How to change administrator password of DB and creation of normal user

Hi, Check this out here.

    MSSQL - SA  (Administrator)
    Oracle OEE - SYS, SYSTEM  (SYSDBA)
 
To change sys password of OEE:
1. Change the sqlnet.ora
sqlnet.authentication_services = NTS
2. Restart OEE service,
3. In command prompt, sqlplus / as sysdba;
Alter user sys identified by "password" ;

4.To test the changed password, sqlplus sys/"pasword"@DBname as sysdba;


To create normal user :
1. In cmd,  sqlplus sys/"pasword"@DBname as sysdba;
2. Create user smyeong identified by "password";
3. grant connect, resource to smyeong;
4. grant dba,resource to smyeong;

Hope that helps....

AS400 - IBM I series - How to create outq for printing

Basically, create OUTQ, start OUTQ and print to remote printer

1 To create :
 CRTOUTQ OUTQ(QGPL/outqname) RMTSYS(*INTNETADR) RMTPRTQ(`')

CNNTYPE(*IP) DESTTYPE(*OTHER) TRANSFORM(*NO) INTNETADR(`IP Address of 
your printer')

2. To start :

STRRMTWTR outqname

Eg. STRRMTWTR QGPL/JDE_HP4PSB

If require to release, pls type : RLSOUTQ outqname

3.Print to remote printer

1. End the remote writer to which the output queue is connected.
2. Use the Change Output Queue (CHGOUTQ) command to change the Display Options (DSPOPT) parameter so that it contains the value XAIX.
3. Restart the remote writer.
   The output queue should now be able to send multiple copies of the documents to the remote printer.

Script to stop Oracle DB

Below is one of the scripts :

For shudown/startuo database read :
http://it.toolbox.com/wiki/index.php/Start_/_Shut_Oracle_with_a_Click_%28Windows_Script%29
http://it.toolbox.com/blogs/enterprise-apps/how-to-start-and-stop-oracle-in-windows-environment-33926

Backup Script for windows
http://pierreroussin.wordpress.com/2008/10/28/rman-backup-script-on-windows/

echo shutdown immedate > shutdown.sql
echo exit >> shutdown.sql
sqlplus / as sysdba @ shutdown.sql
del shutdown.sql
exit

Oracle DB is down

Pls try below steps :

1.Go to db server.

Service stop.

①OEM(OracleEnterpriseManager)　(net start OracleDBConsole[SID])

②net start OracleOraDb11g_home1TNSListener[listenername])

③net start OracleService[SID])

Service start.

①net start OracleService[SID])

②net start OracleOraDb11g_home1TNSListener[listenername])

③OEM(OracleEnterpriseManager)　(net start OracleDBConsole[SID])

2. Go to CMD :

Set oracle_home and oracle_sid

Type :  sqlplus username/password@SID as sysdba;

; shutdown immdiate;

startup;

 

- check the alert.log from the DB server

Simple Insert statement

There are 2 ways of deleting records from table in database.
Option 1 :

truncate table JDESY910.F00950;  or

delete JDESY910.F00950;

 insert into JDESY910.F00950 select * from SMYEONG.F00950B;

Option 2 :

drop JDESY910.F00950;

Create table "JDESY910".F00950 as select * from "SMYEONG"."F00950B" where '1' = '1'

* this method will not create indexes and not good for big transaction table

Hope that helps....

Simple SQL - Union statement

SELECT orderno FROM saletable
UNION
SELECT orderno FROM saledetail

This is to show distinct value from the 2 tables by merging those duplicated values.

Eg, 
 OrderNo.SaleTable  
     SO1   
     SO2
OrderNo.SaleDetail
    SO1                  
    SO1
    SO2


output will be:  SO1, SO2  total record is 2


SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

This is to include those duplicated values from above tables:

output will be :  5 records

Simple SQL Select statement

Select statement with ascending or descending order :

Select field,field2  from Table;
Select * from Table;
select * from TABLE order by Field Asc;
select * from TABLE order by Field Desc;


Hope you like it...

How to export data from SQLplus into file

Hi, below are the guide on how to go about this trick...

Simple text

spool <filename> create;

<statement(s)>

spool off;

HTML

set markup html on

spool <filename> create;

<statement(s)>

spool off;

set markup html off

For Excel export :
http://stackoverflow.com/questions/6017863/create-an-excel-spreadsheet-from-a-oracle-database