Sybase Dump verification

TAKEN DUMP
1> load database pm from '/OracleDumps/opctlsy2/pm.verific.dmp' with headeronly
2>
Backup Server session id is: 14. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'pm100050E6BD ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/pm.verific.dmp'
This is a database dump of database ID 4, name 'pm', from Jan 5 2010 4:24PM. ASE version: Adaptive Server Enterprise/15.0.2/EBF 14328/P/Sun_svr4/OS 5.8/ase1502/2486/64-bit/FBO/Thu May 24 12:. Backup Server version: Backup Server/15.0.2/Sun_svr4/OS 5.8/ase1502/2902/32-bit/OPT/Thu May 24 08:04:12 2007. Database page size is 2048.
Database contains 15360 pages; checkpoint RID=(Rid pageid = 0x7e0; row num = 0x2); next object ID=1801054421; sort order ID=50, status=0; charset ID=1.
Database log version=7; database upgrade version=35.
segmap: 0x00000007 lstart=0 vstart=[vpgdevno=8 vpvpn=0] lsize=5120 unrsvd=3546
segmap: 0x00000007 lstart=5120 vstart=[vpgdevno=23 vpvpn=0] lsize=10240 unrsvd=10193
1> load database pm from '/OracleDumps/opctlsy2/pm.verific.dmp' with listonly
2>
Backup Server session id is: 16. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.34.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Volume name:' '
Backup Server: 4.36.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
File name: 'pm100050E6BD '
Create date & time: Tuesday, Jan 5, 2010, 16:24:29
Expiration date & time: Tuesday, Jan 5, 2010, 00:00:00
Database name: 'pm '
Backup Server: 6.30.1.3: Device /OracleDumps/opctlsy2/pm.verific.dmp: Volume cataloguing complete.
1> load database pm from '/OracleDumps/opctlsy2/pm.verific.dmp' with listonly=full
2>
Backup Server session id is: 18. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.35.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Label name: 'VOL1'
Volume id: ' '
Access code: ' '
Reserved: ' '
Compression level: '0'
Owner id: ' '
Reserved: ' '
Labeling version: 7
Backup Server: 4.37.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Label id: 'HDR1'
File name: 'pm100050E6BD '
Stripe count: 1
Device typecount: 1
Archive volume number: 1
Stripe position: 0
Generation number: 0001
Generation version: 00
Backup Server: 4.148.1.1:
Create date & time: Tuesday, Jan 5, 2010, 16:24:29
Expiration date & time: Tuesday, Jan 5, 2010, 00:00:00
Access code: ' '
File block count: 0
Sybase id string: 'Sybase '
Reserved: ' '
Backup Server: 4.174.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Label id: 'HDR2'
Record format: 'F'
Max. bytes/block: 65536
Record length: 02048
Backup format version: 03
Reserved: ' B'
Database name: 'pm '
Buffer offset length: 00
Async. database I/Os: '000000002'
Max. database I/O size: '0000128'
Number of I/O Buffers: '03'
database page size in KiloBytes: '0000002'
Reserved: ' '
Backup Server: 4.37.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Label id: 'EOF1'
File name: 'pm100050E6BD '
Stripe count: 1
Device typecount: 1
Archive volume number: 1
Stripe position: 0
Generation number: 0001
Generation version: 00
Backup Server: 4.148.1.1:
Create date & time: Tuesday, Jan 5, 2010, 16:24:29
Expiration date & time: Tuesday, Jan 5, 2010, 00:00:00
Access code: ' '
File block count: 1483
Sybase id string: 'Sybase '
Reserved: ' '
Backup Server: 4.174.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Label id: 'EOF2'
Record format: 'F'
Max. bytes/block: 65536
Record length: 02048
Backup format version: 03
Reserved: ' B'
Database name: 'pm '
Buffer offset length: 00
Async. database I/Os: '000000002'
Max. database I/O size: '0000128'
Number of I/O Buffers: '03'
database page size in KiloBytes: '0000002'
Reserved: ' '
Backup Server: 4.154.1.1: Device '/OracleDumps/opctlsy2/pm.verific.dmp':
Label id: 'EOF3'
Total data stored on the volume: 2966 KiloBytes
Backup Server: 6.30.1.3: Device /OracleDumps/opctlsy2/pm.verific.dmp: Volume cataloguing complete.
CORRUPTED THE DUMP ;)
1> load database pm from '/OracleDumps/opctlsy2/pm.verific.dmp' with headeronly
2>
Backup Server session id is: 21. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.12.2.1: Label validation error: HDR1 label not found.
Backup Server: 6.31.2.4: Volume rejected.
Backup Server: 1.14.2.2: Unrecoverable I/O or volume error. This DUMP or LOAD session must exit.
Backup Server: 6.32.2.3: /OracleDumps/opctlsy2/pm.verific.dmp: volume not valid or not requested (server: , session id: 21.)
Backup Server: 1.14.2.4: Unrecoverable I/O or volume error. This DUMP or LOAD session must exit.
Msg 8009, Level 16, State 1:
Server 'GALLARDO', Line 1:
Error encountered by Backup Server. Please refer to Backup Server messages for details.
1> load database pm from '/OracleDumps/opctlsy2/pm.verific.dmp' with listonly
2>
Backup Server session id is: 23. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.12.2.1: Label validation error: HDR1 label not found.
Backup Server: 6.31.2.4: Volume rejected.
Backup Server: 6.32.2.3: /OracleDumps/opctlsy2/pm.verific.dmp: volume not valid or not requested (server: , session id: 23.)
Backup Server: 1.14.2.4: Unrecoverable I/O or volume error. This DUMP or LOAD session must exit.
1> load database pm from '/OracleDumps/opctlsy2/pm.verific.dmp' with listonly = full
2>
Backup Server session id is: 25. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.12.2.1: Label validation error: HDR1 label not found.
Backup Server: 6.31.2.4: Volume rejected.
Backup Server: 6.32.2.3: /OracleDumps/opctlsy2/pm.verific.dmp: volume not valid or not requested (server: , session id: 25.)
Backup Server: 1.14.2.4: Unrecoverable I/O or volume error. This DUMP or LOAD session must exit.

Mount Unmount databases in Sybase

1> unmount database mydb to '/OracleDumps/opctlsy2/sybase/mydb.mnfst'
2>
00:00000:00011:2008/07/29 17:02:43.76 kernel Deactivating virtual device 1, '/OracleDumps/opctlsy2/sybase/data/mdev.dat'.
00:00000:00011:2008/07/29 17:02:43.76 kernel Deactivating virtual device 3, '/OracleDumps/opctlsy2/sybase/data/mdev1.dat'.
1> 1>
1>
Password:
Msg 2401, Level 11, State 2:
Character set conversion is not available between client character set 'iso_1' and server character set 'ascii_8'.
No conversions will be done.

1> sp_helpdb
2>
name db_size owner dbid created status
-------------- ------------- ----- ----- ------------ --------------------------------------------------------------------
master 99.5 MB sa 1 Jan 01, 1900 mixed log and data
model 3.0 MB sa 3 Jan 01, 1900 mixed log and data
sybsystemdb 3.0 MB sa 31513 Jul 16, 2008 mixed log and data
sybsystemprocs 170.0 MB sa 31514 Feb 19, 2008 trunc log on chkpt, mixed log and data
tempdb 4.0 MB sa 2 Jul 29, 2008 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
(1 row affected)
(return status = 0)
1> mount database all from '/OracleDumps/opctlsy2/sybase/mydb.mnfst'
2>
00:00000:00012:2008/07/29 17:04:17.36 kernel Initializing virtual device 1, '/OracleDumps/opctlsy2/sybase/data/mdev.dat' with dsync 'on'.
00:00000:00012:2008/07/29 17:04:17.36 kernel Virtual device 1 started using asynchronous i/o.
00:00000:00012:2008/07/29 17:04:17.36 kernel Initializing virtual device 3, '/OracleDumps/opctlsy2/sybase/data/mdev1.dat' with dsync 'on'.
00:00000:00012:2008/07/29 17:04:17.36 kernel Virtual device 3 started using asynchronous i/o.
00:00000:00012:2008/07/29 17:04:17.37 server Log contains all committed transactions until 2008/07/29 16:55:49.67 for database mydb.
Started estimating recovery log boundaries for database 'mydb'.
Database 'mydb', checkpoint=(2567, 13), first=(2567, 13), last=(2567, 13).
Completed estimating recovery log boundaries for database 'mydb'.
Started ANALYSIS pass for database 'mydb'.
Completed ANALYSIS pass for database 'mydb'.
Started REDO pass for database 'mydb'. The total number of log records to process is 1.
Completed REDO pass for database 'mydb'.
Recovery of database 'mydb' will undo incomplete nested top actions.
Started recovery checkpoint for database 'mydb'.
Completed recovery checkpoint for database 'mydb'.
Started filling free space info for database 'mydb'.
Completed filling free space info for database 'mydb'.
Started cleaning up the default data cache for database 'mydb'.
Completed cleaning up the default data cache for database 'mydb'.
MOUNT DATABASE: Completed recovery of mounted database 'mydb'.
1> sp_helpdb
2>
name db_size owner dbid created status
-------------- ------------- ----- ----- ------------ --------------------------------------------------------------------
master 99.5 MB sa 1 Jan 01, 1900 mixed log and data
model 3.0 MB sa 3 Jan 01, 1900 mixed log and data
mydb 10.0 MB sa 4 Jul 29, 2008 offline
sybsystemdb 3.0 MB sa 31513 Jul 16, 2008 mixed log and data
sybsystemprocs 170.0 MB sa 31514 Feb 19, 2008 trunc log on chkpt, mixed log and data
tempdb 4.0 MB sa 2 Jul 29, 2008 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
(1 row affected)
(return status = 0)
1>

Fixing Corrupt index on system table

1> sp_logiosize 'default'
2>
Msg 644, Level 21, State 5:
Procedure 'sp_logiosize', Line 313:
Index row entry for data row id (817, 15) is missing from index page 1009 of index id 2 of table 'sysattributes' in database 'sag'. Xactid is (10215,24). Drop and re-create the index.
1> sp_dboption sag, 'single', true
2>
Database option 'single user' turned ON for database 'sag'.
Running CHECKPOINT on database 'sag' for option 'single user' to take effect.
(return status = 0)
1>
1> use sag
2>
1> checkpoint
1> use sag
2>
1> sp_fixindex sag,sysattributes,2
2>
There are 22.000000 rows in 1 pages for object 'sysattributes'.
Non-clustered index successfully restored for object 'sysattributes' in 'sag' database.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
(return status = 0)

1> sp_logiosize 'default'
2>
The transaction log for database 'sag' will use I/O size of 4 Kbytes.
(return status = 0)
1>

Dropping a Corrupt Table from Sybase

1> declare @obj int
2> select @obj = id from sysobjects where name = 'dddd'
3> delete syscolumns where id = @obj
4> delete sysindexes where id = @obj
5> delete sysobjects where id in (select constrid from sysconstraints where tableid = @obj)
6> delete sysdepends where depid = @obj
7> delete syskeys where id = @obj
8> delete syskeys where depid = @obj
9> delete sysprotects where id = @obj
10> delete sysconstraints where tableid = @obj
11> delete sysreferences where tableid = @obj
12> delete sysattributes where object = @obj
delete syspartitions where id = @obj
13> 14> delete sysstatistics where id = @obj
15> delete systabstats where id = @obj
16> delete syscomments where id in (select id from sysobjects where deltrig = @obj)
17> delete syscomments where id in (select id from sysobjects where instrig = @obj)
18> delete syscomments where id in (select id from sysobjects where updtrig = @obj)
19> delete sysprocedures where id in (select id from sysobjects where deltrig = @obj)
20> delete sysprocedures where id in (select id from sysobjects where instrig = @obj)
21> delete sysobjects where deltrig = @obj
22> delete sysobjects where instrig = @obj
23> delete sysobjects where updtrig = @obj
24> delete sysobjects where id = @obj
25> delete sysstatistics where id = @obj
26> delete systabstats where id = @obj
delete syspartitionkeys where id = @obj
27>
28>
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(12 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)
1> commit tran
2>

Finallly dont forget to destroy the object descriptor from the metadata cache :

1> select object_id('dddd')
2>
-----------
828526954
(1 row affected)
1> sp_monitorconfig 'open objects'
2>
Usage information at date and time: Apr 21 2009 9:54AM.
Name Num_free Num_active Pct_act Max_Used Reuse_cnt
------------------------- ----------- ----------- ------- ----------- -----------
number of open objects 948 52 5.20 52 0
(1 row affected)
(return status = 0)
1> dbcc cacheremove(5,828526954)
2>
1> sp_monitorconfig 'open objects'
2>
Usage information at date and time: Apr 21 2009 9:54AM.
Name Num_free Num_active Pct_act Max_Used Reuse_cnt
------------------------- ----------- ----------- ------- ----------- -----------
number of open objects 949 51 5.10 52 0
(1 row affected)
(return status = 0)
1> select object_id('dddd')
2>
-----------
NULL
(1 row affected)
1>

Delete Trigger example in Sybase

1> sp_helptext empage_delete_restrict
2>
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create trigger empage_delete_restrict on empage
for delete
as
begin
print 'Delete of records not allowed in this table'
rollback transaction
end

(1 row affected)
(return status = 0)
1> select * from empage
2>
empid empage
----------- -----------
654 23
76 45
6 43
100 29
65 60
(5 rows affected)
1> delete from empage where empid = 100
2>
Delete of records not allowed in this table
1> select * from empage
2>
empid empage
----------- -----------
654 23
76 45
6 43
100 29
65 60
(5 rows affected)
1>

Update Trigger Example Sybase

create trigger empdata_to_upd_empage on empdata
for update
as
if update (empid)
begin
update empage set empage.empid = inserted.empid
from
inserted,empage,deleted
where deleted.empid=empage.empid
end

(1 row affected)
(return status = 0)
1> select * from empdata
2>
empname empid
-------------------- -----------
k 654
m 76
o 6
t 98
w 65
(5 rows affected)
1> select * from empage
2>
empid empage
----------- -----------
654 23
76 45
6 43
98 29
65 60
(5 rows affected)

1> update empdata set empid=100 where empname='t'
2>
(1 row affected)
1> select * from empdata
2>
empname empid
-------------------- -----------
k 654
m 76
o 6
t 100
w 65
(5 rows affected)
1> select * from empage
2>
empid empage
----------- -----------
654 23
76 45
6 43
100 29
65 60
(5 rows affected)
1>

Sybase ASE 15.0.2 Job Scheduler Setup

JOB SCHEDULER IN SYBASE


Step 1 Create a raw device for sybmgmtdb database


/opt/VRTSvxfs/sbin/qiomkfile -s 75m /OracleDumps/opctlsy2/sybmgmtdev
aloncvc1004:opctlsy2 >/opt/VRTSvxfs/sbin/qiomkfile -s 75m /OracleDumps/opctlsy2/sybmgmtdev

Step 2 Initialise unix raw device to create allocation units for dataserver

1> disk init
2> name=sybmgmtdev,
3> physname='/OracleDumps/opctlsy2/sybmgmtdev',
4> size='75M'


Step 3 Run the installjsdb script in $SYBASE/$SYBASE-ASE/scripts which will create the database sybmgmtdb on the device sybmgmtdev

aloncvc1004:opctlsy2 >isql -Usa -SGALLARDO -iinstalljsdb -oinstalljsdb.out
Password:*****

Starting the setup of sybmgmtdb.
Using device sybmgmtdev for the sybmgmtdb database.
CREATE DATABASE: allocating 33280 logical pages (65.0 megabytes) on disk
'sybmgmtdev'.
CREATE DATABASE: allocating 5120 logical pages (10.0 megabytes) on disk
'sybmgmtdev'.
Caution: You have set up this database to include space on disk 55 for both
data and the transaction log. This can make recovery impossible if that disk
fails.
Database 'sybmgmtdb' is now online.
Database option 'trunc log on chkpt' turned ON for database 'sybmgmtdb'.
Running CHECKPOINT on database 'sybmgmtdb' for option 'trunc log on chkpt' to
take effect.
(return status = 0)
Database option 'select into/bulkcopy/pllsort' turned ON for database
'sybmgmtdb'.
Running CHECKPOINT on database 'sybmgmtdb' for option 'select
into/bulkcopy/pllsort' to take effect.
(return status = 0)
Executing checkpoint in sybmgmtdb
Database log is 5120 pages, 30% log free is at 1536 pages, the last chance
threshold is at 376 pages.
installing sp_js_logthreshold at 1536 pages.
Adding threshold for segment 'logsegment' at '1536' pages.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 0)
Created js_jobs.
Created js_commands.
Created js_schedules.
Created js_scheduledjobs.
Created js_seqsql.
Created js_callouts.
Created js_history.
Created js_output.
Created js_keys.
Created js_templates.
Created js_xml.
Created js_daynames.

Step 4 Add the below entry into interfaces file, note that port you choose should be avaialable and not in use


GALLARDO_JSAGENT
master tcp ether opctlsy2 5030
query tcp ether opctlsy2 5030

Step 5 Add the server into sysserver table

1> sp_addserver SYB_JSAGENT,null,GALLARDO_JSAGENT
2>
Adding server 'SYB_JSAGENT', physical name 'GALLARDO_JSAGENT'
Server added.
(return status = 0)

Step 6 Add the local server as loopback server onto sysservers as this will be referred by JS Scheduler and JS Agent


1> sp_addserver loopback,ASEnterprise,GALLARDO
2>
Adding server 'loopback', physical name 'GALLARDO'
Server added.
(return status = 0)

1> sp_helpserver
2>
name network_name class status id cost
----------- ---------------- ------------ ------------------------------------------------------------------------ -- ----
FERRARI FERRARI ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 4 1000
GALLARDO GALLARDO ASEnterprise 0 0
GALLAREP GALLAREP sql_server no timeouts, no net password encryption, writable , rpc security model A 5 1000
GALLARIBO GALLARIBO ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 6 1000
SYB_BACKUP GALLARDO_BS ASEnterprise timeouts, no net password encryption, writable , rpc security model A 1 0
SYB_JSAGENT GALLARDO_JSAGENT ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 7 1000
SYB_JSTASK GALLARDO ASEnterprise timeouts, no net password encryption, writable , rpc security model A 8 1000
local GALLARDO ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 3 1000
loopback GALLARDO ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 2 1000
(return status = 0)



Step 7 Set up Sybase configuration parameters to start Job Scheduler

1> sp_configure 'job'
2>
Msg 17411, Level 16, State 1:
Server 'GALLARDO', Procedure 'sp_configure', Line 278:
Configuration option is not unique.

Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
enable job scheduler 0 0 0 0 switch dynamic
job scheduler interval 1 0 1 1 seconds dynamic
job scheduler tasks 32 0 32 32 number dynamic
maximum job output 32768 0 32768 32768 bytes dynamic

(1 row affected)
(return status = 1)
1> sp_configure 'enable job scheduler', 1
2>
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
enable job scheduler 0 0 1 1 switch dynamic

(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is dynamic.
Changing the value of 'enable job scheduler' to '1' increases the amount of memory ASE uses by 18694 K.
(return status = 0)
1>


Step 8 Start up Sybase Job Scheduler

Even a server recycle will start the JS with following message in sybase log

00:00000:00013:2009/08/03 14:39:18.94 kernel Initializing Job Scheduler Task
00:00000:00013:2009/08/03 14:39:19.01 kernel Installed Job Scheduler sequencer code version 0.27 - 27 tokens
00:00000:00013:2009/08/03 14:39:19.12 kernel Job Scheduler Task started with a clean 'js_history' table
00:00000:00013:2009/08/03 14:39:20.20 kernel Job Scheduler Task state set to running, startcount 1.
00:00000:00013:2009/08/03 14:39:21.20 kernel Job Scheduler Task connected with Agent on port 5030

OR


1> sp_js_wakeup 'start_js', 1
2>
(1 row affected)
(return status = 1)
1>

Step 9 Add a login with js_admin_role, js_user_role, js_client_role which you will use to kick and create jobs

1> sp_addlogin job_scheduler, job_scheduler, sybmgmtdb,NULL,'JS ADMIN ROLE'
2>
Password correctly set.
Account unlocked.
New login created.
(return status = 0)
1> use sybmgmtdb
2>
1> sp_adduser job_scheduler,job_scheduler
2>
New user added.
(return status = 0)
1>

1> sp_role 'grant',js_admin_role,job_scheduler
2>
Authorization updated.
(return status = 0)
1> sp_role 'grant',js_user_role,job_scheduler
2>
Authorization updated.
(return status = 0)
1>


Step 10 Need to register the login as a remote login to all the target servers where you need to run jobs out of JS


1> sp_addexternlogin FERRARI,job_scheduler,job_scheduler,job_scheduler
2>
User 'job_scheduler' will be known as 'job_scheduler' in remote server 'FERRARI'.
(return status = 0)
1>

For Local server add the following

1> sp_addexternlogin loopback,job_scheduler,job_scheduler,job_scheduler
2>
User 'job_scheduler' will be known as 'job_scheduler' in remote server 'loopback'.
(return status = 0)
1>


Step 11 If you use Sybase Central you can install the forllowing sps and templates using which you can create jobs for dump, reorg, statistics, rebuild etc


TEMPLATES

aloncvc1004:opctlsy2 >pwd
/OracleDumps/opctlsy2/sybase/ASE-15_0/jobscheduler/Templates/sprocs
aloncvc1004:opctlsy2 >


aloncvc1004:opctlsy2 >./installTemplateProcs 'GALLARDO' 'sa' '*****'
Creating generic stored procedures used by Sybase Job Scheduler Template jobs
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_check_reorg_space has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_database_names has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_index_names has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_datachange has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_datestr dropped
sp_jst_get_datestr has been created
Generic stored procedure used by Sybase Job Scheduler Template
jobs
sp_jst_get_freespace has been created

TEMPLATE PROCS

aloncvc1004:opctlsy2 >./installTemplateProcs 'GALLARDO' 'sa' '*****'
Creating generic stored procedures used by Sybase Job Scheduler Template jobs
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_check_reorg_space has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_database_names has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_index_names has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_datachange has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_datestr dropped
sp_jst_get_datestr has been created
Generic stored procedure used by Sybase Job Scheduler Template
jobs
sp_jst_get_freespace has been created
Creating support Stored Procedure for Sybase Reconfigure Metadata Cache Template
created procedure sp_jst_get_mdcache_type
sp_jst_get_spaceused has been created
Creating Support Stored Procedure for Sybase Reconfigure Metadata Cache Template
created procedure sp_jst_reconf_mdcache_type
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_verion_num has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_purge_alphachars has been created
sp_jst_valid_ase_version has been created
Creating job-specific stored procedures used by Sybase Job Scheduler Templates
Creating Sybase Delete Statistics Template stored procedures
sp_jst_delete_stats has been created
sp_jst_dump_db_to_disk has been created
Creating Stored Procedures for Sybase Backup Database to Disk Template
sp_jst_dump_dbs_to_disk has been created
Creating Sybase Back-up Log Template stored procedures
Creating Stored Procedures for Sybase Dump Logs Template
sp_jst_dump_tran_log_to_disk has been created
sp_jst_chk_row_threshold has been created
sp_jst_chk_time_threshold has been created


Step 12 Go ahead and create jobs and schedule them :)

Create job

declare @jobcmd varchar(255)
select @jobcmd='jcmd=YOUR COMMAND OR PROC,server=GALLARDO'
exec sp_sjobcreate 'sjname=3_job', @jobcmd

Attach a schedule

1> sp_sjobmodify @name='first_job', @option='sname=mysched'
2>
(return status = 0)
1> sp_sjobmodify 'sname=mysched' , @option='repeats=1day, starttime=03:00pm'
2>
(return status = 0)
1>

sp_sjobhelp


sjob_id: 6 name: 'first_job'
owner : sa
created : Aug 4 2009 2:15PM
state : enabled
job name : 7 - 'job_7'
schedule name : 7 - 'mysched'
server : GALLARDO
-- job --------:
description :
owner : sa
created : Aug 4 2009 2:15PM
-- schedule ---:
description :
owner : sa
created : Aug 4 2009 2:15PM
repeating : every day
starttime : 15:00
startdate : 04 Aug 2009


(1 row affected)
(return status = 0)
1> sp_sjobcmd 'first_job', 'list'
2>
job_id job_name jcmd_seqno jcmd_text
7 job_7 0 use master
7 job_7 1
7 job_7 2 go
7 job_7 3 exec sp_myproc
7 job_7 4
7 job_7 5 go



Default log location is the server log location :

GALLARDO_JSAGENT.log

Parameter Passing in proxy tables designed to store sp output

A. Pre requisites: Need to have cis properties enabled, loopback server added in sysservers, and setting up an externlogin to loopback from your local
B. Create existing table :
create existing table procinfo ( procname varchar(20), owner varchar(10), tranmode varchar(20) , _procname varchar(767) null )
external procedure at "loopback.sybsystemprocs.dbo.sp_procxmode"
Here the last column in actually the parameter to the procedure sp_procxmode with same name but distinguished by an _ ( underscore )
You can use this in the table select where clause as shown later:

C. Now start selecting ;)

1> select * from procinfo
2>
procname owner tranmode _procname
-------------------- ---------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sp_activeroles dbo Any Mode NULL
sp_add_qpgroup dbo Any Mode NULL
sp_add_resource_limi dbo Any Mode NULL
This will take the _procname as NULL as no where clause passed and thus give a listing of sp_procxmode output as stored in procinfo table


1> select * from procinfo where procname='sp_listsuspect_db'
2>
procname owner tranmode _procname
-------------------- ---------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sp_listsuspect_db dbo Any Mode NULL
This will pass the parameter @procname = 'sp_listsuspect_db' as this has been mentioned in where clause and return only for the procedure.
We can use this feature to capture results of stored procedure in tables by providing different parameters…
Cheers,
Ajit

Useful ASE stored procedures to check various constraints

You can use the below set of stored procedures to find the same

Basic Level sp_helpindex tablename
Details
sp_fkeys tablename Gives details of foreign keys on the table having primary keys
sp_pkeys tablename Give details of the primary keys
sp_helpcontraint tablename, detail Gives details of all contraints on the table
sp_helpkey tablename Gives details of the keys associated with the table

Additional Procedure cache requirements for cross platform dump and load

Below Procedure cache sizing formula, we can keep this handy with us Approximate calculation for memory needed in procedure cache:
2KB(context structure) -- Required for loading databases ( also in Cross Platform dump load )
+ 16KB(translation dictionary for system tables) ( Suppose we load into a different byte storage architecture in Cross Platform then system tables translational dictionary will be built for conversion)
+ 146KB(work memory to build translation dictionary for system tables and user tables) ( This will be required to build up the translational dictionary for initiating Cross Platform Load )
+ ((# of user tables) * 20 bytes) ( Normal estimation for user tables )
+ ((# of indexes on user tables) * 8 bytes) ) ( Normal estimation for user table indexes )
+ ((# of columns on user tables which needs translation + # of columns in index keys on user tables which needs translation) * 12 bytes) ( Number of columns of tables or indexes which have to be translated )

Example : 10,000 user tables + 40,000 indexes + 400,000 columns = 164KB + 200KB + 320 KB + 4.8 MB = 5.5MB

Pagelinkage APL - Another way to check for fragmentation

SELECT name, first FROM sysindexes WHERE indid not in (0,1) AND id = object_id('PREVPOS')
Non clustered index.

The above gives you the frst page of the leaf level of the nci. Basically our aim is to see how much fragmentation is there in the index leaf level pages

dbcc pglinkage(4,808536,0,2,0,1)
We can view haphazard page chain which increases disk seek times, fragmentation
NCI highly fragmented

This will show you the links from one page to another, and you can observe if their are multiple jumps in between pages in different extets or the pages are organised in a sequential increasing manner.
A new index will have sequential pages, but as there will be more inserts, deletes on index keys, the index gets fragmented.

Storing a stored procedure output into a table

We can use proxy tables to store the output of a procedure into a table. This requires CIS to be enabled on your system


1> create existing table mytable ( LOGIN varchar(20), ALIASEDTO varchar(20),DATABASENAME varchar(20), ALIASUSER varchar(20) )
2> external procedure at "local.sybsystemprocs.dbo.sp_alias"
3>
1> select * from mytable
2>

This will execute the procedure and send you the result in table, which you can use in other queries.

Make sure to add a local server to the sysservers system table.

Tuning Backup Server dump and load operations

We can use following traces to give us a better picture about what goes on when a dump or load happens

Following traces are present as a part of backupserver binary:

BACKUP SERVER TRACES

Backup Server trace flag: 1 is on. This flag does the following: Prints blocksize used during a dump or a load.
Backup Server trace flag: 2 is on. This flag does the following: Prints i/o optimization parameters used during a dump or a load.
Backup Server trace flag: 3 is on. This flag does the following: Allow dumping to the /dev/null device.
Backup Server trace flag: 4 is on. This flag does the following: Prints allocation percentage of the allocation units during dump.
Backup Server trace flag: 5 is on. This flag does the following: Prints the database pagesize used during a dump or a load.

Commands to set them on/off

Using 0 will display the traces set

SYB_BACKUP...qatraceon 0,1,2,3,4,5
SYB_BACKUP...qatraceoff 1,2,3,4,5

Findings:

Increasing stripes upto a certain amount can minimise number of io made or time taken during a dump or load by sybmultbuf to read /write on dumps/devices.
Increasing shared memory available to backup server via -m on backup run server can also help on performance tuning of dumps and loads.

There are few important terms in traces which give us an idea about the io sizes, number of zones used

Dbiosize the database io size to be used on the dump file during dumping a particular stripe
Iocount Total number of IO made for the dump or load session
Zonesize Not very clear but aids performance if the numzones are higher in number
Blocksize the block size to be used
Numzones the more the numzones , the better is performance ( ranges from 1 to 5 )



Dump Cases Considered under tracing for qatraceon 1,2 and 3 :

A.dumping into one stripe with shared memory 100MB

1> dump database sag to '/OracleDumps/opctlsy2/sag.dmp'

2> Backup Server session id is: 33. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.173.1.1: The database pagesize is '2048' bytes.
Backup Server: 6.28.1.1: Dumpfile name 'sag1000609C09 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp'
Backup Server: 4.166.1.1: Using dbiosize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.165.1.1: Using iocount of 7 for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.166.1.2: Using zonesize of 458752 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.165.1.2: Using numzones of 2 for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.

Notice that the iocount is 7, dbiosize is 65536 bytes, numzones are 2

B. dumping into one stripe with shared memory double

1> dump database sag to '/OracleDumps/opctlsy2/sag.dmp'
2>
Backup Server session id is: 12. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A4F5 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp'
Backup Server: 4.166.1.1: Using dbiosize of 262144 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.165.1.1: Using iocount of 7 for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.166.1.2: Using zonesize of 1835008 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.165.1.2: Using numzones of 3 for device /OracleDumps/opctlsy2/sag.dmp.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 4030 ms.

Notice the iocount 7 but dbiosize 262144 bytes, numzones 3, time is 4030 ms

C.dumping on to 2 stripes

dump database sag to '/OracleDumps/opctlsy2/sag.dmp1' stripe on '/OracleDumps/opctlsy2/sag.dmp2'
Backup Server session id is: 33. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A9C7 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp1'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.1: Using iocount of 3 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.2: Using zonesize of 1572864 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A9C7 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp2'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.1: Using iocount of 3 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.2: Using zonesize of 1572864 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 3980 ms.

Notice iocount reduced to 6, numzones are 4, dbiosize is 524288, time taken 3980 ms - Performance better as less io, less time


D.dumping to 3 stripes

dump database sag to '/OracleDumps/opctlsy2/sag.dmp1' stripe on '/OracleDumps/opctlsy2/sag.dmp2' stripe on '/OracleDumps/opctlsy2/sag.dmp3'
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A5E2 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp2'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.1: Using iocount of 2 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A5E2 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp1'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.1: Using iocount of 2 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A5E2 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp3'
Backup Server: 4.166.1.1: Using dbiosize of 524288 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.165.1.1: Using iocount of 2 for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Adaptive Server cpu time: 100 ms. Adaptive Server elapsed time: 4026 ms.

Notice iocount total 6, dbiosize 524288 but zonesize has reduced per dump still it uses numzones as 4, time is 4026ms


E. dumping to 4 stripes

dump database sag to '/OracleDumps/opctlsy2/sag.dmp1' stripe on '/OracleDumps/opctlsy2/sag.dmp2' stripe on '/OracleDumps/opctlsy2/sag.dmp3' stripe on '/OracleDumps/opctlsy2/sag.dmp4'
Backup Server session id is: 26. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A65F ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp4'
Backup Server: 4.166.1.1: Using dbiosize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.165.1.1: Using iocount of 1 for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp4.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A65F ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp1'
Backup Server: 4.166.1.1: Using dbiosize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.1: Using iocount of 1 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp1.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A65F ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp2'
Backup Server: 4.166.1.1: Using dbiosize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.1: Using iocount of 1 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp2.
Backup Server: 6.28.1.1: Dumpfile name 'sag100060A65F ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sag.dmp3'
Backup Server: 4.166.1.1: Using dbiosize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.165.1.1: Using iocount of 1 for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.166.1.2: Using zonesize of 1048576 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.165.1.2: Using numzones of 4 for device /OracleDumps/opctlsy2/sag.dmp3.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /OracleDumps/opctlsy2/sag.dmp3.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 3983 ms.

Notice the iocount as 4 now, dbiosize of 1048576 bytes, numzones as 4

Similarly we can tune the loads based on the traced optimisation parameters.