Database Update Vikram Kumar B T CSY Aug 2001
•Increase limits of a database Enhancements included in 6.5 Exp. 2 & 7.0 Exp.
•New version of DBSCHEMA allows higher limits Increase limits of a database •Rootfile version will be ‘C’5 if datasets/items/paths exceed old limits •Applications need to increase buffer size for DBINFO mode 103, 203, 204 and 301 •For debugging purpose, DBUTIL flag available >>ENABLE basename FOR OLDINFOLIMITS •DBCONTROL mode 20 allows applications to negate the DBUTIL setting in the specific DBOPEN •Increase number of datasets per database to 240 •Increase number of items per database to 1200 •Increase
•Use record number instead of record name as the internal pointers Support of dataset size > 80 GB •Need to specify $CONTROL LARGESET in DBSCHEMA to create a database in record number format •Rootfile version will be ‘C’6 to denote the database is in record number format •Only one format allowed for a given database •Use DBBIGSET.PUB.
•Record number is preceded by a # sign •Default record number is a decimal number Find by Record number in QUERY •Record number can be an octal (preceded by %) or a hexadecimal value (preceded by $) •Examples: to read the fifteenth record in the dataset INVOICES • FIND INVOICES.#15 • FIND INVOICES.#%17 • FIND INVOICES.#$f User can find a specific record by giving a record number after the dataset name >FIND datasetname.#recordnumber QUELXD6 Oct 2000 D.03.
•New command VERBOSE to enable the report •New command TERSE to disable the report (this is default) Display percent completion in QUERY •Enhance command SHOW to show VERBOSE and SHOW ALL to display all options •Use SETVAR HP_QUERY_PROGRESS_INTERVAL nnn to set the time interval.
Other QUERY Patches •QUELXP5 Apr 2001 D.03.20 Report Reals trailing zero fix QUERYCM is no longer being updated (still 3.17) and will be deleted in the future. •QUELXE9 Dec 2000 D.03.18 Business Basic Floating Decimal Fix •QUELXJ0 Feb 2001 D.03.19 Progress Reporting Enhancement VERBOSE/TERSE Commands SHOW Improved - All option Performance enhancements Query has done a FIND of 16,700,000 records (the FIND Limit) on a 979-100 under MPE/iX 6.5 with Jamaica discs in under 14 minutes.
•Large file dataset •TurboIMAGE scalability II •Increase Allbase limits Enhancements ready to test/ submit •Allow one store procedure to call another store procedure •IMAGE/SQL NOAUTOs •(Allbase Auto-increment)
•$CONTROL LFDS in DBSCHEMA to create large file dataset, which is default •May need $CONTROL LARGESET if the capacity is too big Large file data set •The maximum large file dataset size is 128GB, if exceeds, need to make the dataset jumbo •Large file dataset and jumbo dataset cannot co-exist in the same database •Rootfile version will be ‘C’7 and bit 8 will be true if database has at least one large file dataset •Basic migration tool : DBLOAD/DBUNLOAD to disk For a dataset larger than 4GB, use MPE large
Put/Delete semaphore •Used to serialize DBPUT/DBDELETE/DBUPDATE activities •One semaphore per database TurboIMAGE scalability II Usage of put/delete semaphore •Control the modification to the dataset file label •Cover XM rollback at intrinsic level •Avoid deadlock between data block locks •Manage dynamic dataset expansion DSEM •Group related datasets together •Different group can be modified concurrently Divide Put/Delete semaphore down to block level
0th entry TurboIMAGE scalability II 1st 2nd 3rd 4th E H W M 5th 6th Blocking Factor 7th 8th No. of Entries . . .
Increase Allbase limits Allbase/SQL H0 Under beta testing •Increase number of pages for runtime control block from 2000 to 6000 •Increase number of concurrent transactions from 250 to 750
SP Calling SP CREATE PROCEDURE [Owner.]ProcedureName [LANG = languagename] [(ParameterName ParameterDataType CREATE PROCEDURE [Owner.]ProcedureName [LANG = ProcLangName] [(ParameterDeclaration [, ParameterDeclaration] [...])] [WITH RESULT ResultDeclaration [, ResultDeclaration] [...]] AS BEGIN ProcedureStatement; EXECUTE PROCEDURE [Owner.]ProcedureName [(ActualParameter) [,...]] where ActualParameter = [ParameterName = ] ParamaterValue [OUTPUT [ONLY]] [...
SP Calling SP where ParameterDeclaration = ParameterName ParameterType [LANG = ParameterLanguage] [DEFAULT DefaultValue] [NOT NULL] [OUTPUT] where ResultDeclaration = ResultType [LANG = ResultLanguage] [NOT NULL] Example: CREATE PROCEDURE ReportMonitor (PartNumber CHAR(20) ) AS BEGIN EXECUTE PROCEDURE RemoveParts(:PartNumber); RETURN ::sqlcode ; END ; Constraints : Return Value from Execute Procedure within the Create Procedure can't be assigned to variable.
•For auditing from the log file •Users use the same logon with different session name Extra enhancement •No userident being passed through password/userident parameter of DBOPEN call •DBUTIL flag >>ENABLE basename FOR FORCESESSION Use session name as user identifier in the log file to help the auditors to differentiate users with the same logon.
IMAGE/SQL NOAUTOs • ATTACH [WITH OWNER] [noauto/ auto] [noautosplit/ autosplit] • Lab testing complete • Call for beta testing • Release vehicle to be identified • Automatic masters will not be attached in IMAGE/SQL ‘ATTACH’ command • Automatic split of compound items will not be done • NO AUTOVIEWS investigated • Require Allbase DBCORE changes • Not addressed now
Investigation for making TurboIMAGE thread safe (TOP SIB ITEM) Three requests included: • make TurboIMAGE thread aware and thread safe •make TurboIMAGE forkable •pass ‘base ID’ between processes
Thread Characteristics: •Runtime Control Block (DBUX/DBU) - One per DBOPEN •Thread has its own PIN •Thread has its own stack •Thread shares SR5 space - transaction information - locking information - logging information - current record pointers - opened files - trailer area •Global Variables - qlock_trace - bti_global - ccu_global - chunk_control Problems for not being thread safe: •Open Files •Runtime control block •Global variables •Open files - except rootfile, not using file system intrinsics -
Fork() Characteristics: •Forked process inherits characters from its parent •Forked process has its own SR5 •Duplicate some file system data structures and share some •Runtime Control Block (DBUX/DBU) - Not able to duplicate file system data structure of DBUX and DBU - Many fields not suitable for sharing - State of the process while forking •Global Variables - qlock_trace - bti_global - ccu_global - chunk_control •Open Files Problems for forking: - except rootfile, not using file system intrinsics - m
Pass ‘base ID’ between processes: •threads •forking/forked processes •father/son processes •unrelated processes What is ‘base ID’? •An index into an array of DBUs this process has opened •Array resides in DBUX •Pass ‘base ID’ means share DBU - What if the receiving process already has the same DB opened? - Who is the owner? - What if process terminated? - How to handle user logging?
Options for Runtime Control Block •Pros for Copying - Separate current record pointers • Copying DBUX and DBU - Distinct XM related data structures - ‘accessor entry’ in DBG matches with no.
Options for Runtime Control Block •Pros for Sharing - The key concept for thread is sharing, so does DBU • Copying DBUX and DBU - Share DBU will share open files - Share DBU will share current record pointers • Sharing DBUX and DBU •Cons for Sharing - Need mechanism to control the currency of XM and locking in DBU - Need space control procedures to handle DBU trailer area - Need extra space for log record belongs to different PIN - Break the coherency among the DBOPEN, “accessor entry” and DBU
Feedback: Recommendation: •If share, it shares current record pointer too. It is programmer’s responsibility to handle it correctly •Passing “base ID” between any two processes is too vague and ambitious •For thread, concurrency is the key.
