/*
15.5/EBF 17340 SMP/P/x86_64/Enterprise Linux/ase155/2391/64-bit/OPT/Mon Nov  9 14:15:35 2009

Confidential property of Sybase, Inc.
Copyright 1987, 2009
Sybase, Inc.  All rights reserved.
Unpublished rights reserved under U.S. copyright laws.

This software contains confidential and trade secret information of Sybase,
Inc.   Use,  duplication or disclosure of the software and documentation by
the  U.S.  Government  is  subject  to  restrictions set forth in a license
agreement  between  the  Government  and  Sybase,  Inc.  or  other  written
agreement  specifying  the  Government's rights to use the software and any
applicable FAR provisions, for example, FAR 52.227-19.
Sybase, Inc. One Sybase Drive, Dublin, CA 94568, USA
*/

declare @retval int
exec @retval = sp_version 'installcommit', NULL, '15.5/EBF 17340 SMP/P/x86_64/Enterprise Linux/ase155/2391/64-bit/OPT/Mon Nov  9 14:15:35 2009', 'start'
if (@retval != 0) select syb_quit()
go

declare @script_versnum int
select @script_versnum = 15500
if (@@version_number < @script_versnum)
begin
	print "'installcommit' is being run on an older ASE installation. ASE version '%1!', install scripts version '%2!'.",
		@@version_number, @script_versnum
	select syb_quit()
end
go

declare @do_quit int
select @do_quit = 0
if (check_db_upgrade('master') = 0)
begin
	print "'installcommit' cannot continue, because 'master' database is not upgraded to correct version."
	select @do_quit = 1
end
if (check_db_upgrade('sybsystemdb') = 0)
begin
	print "'installcommit' cannot continue, because 'sybsystemdb' database is not upgraded to correct version."
	select @do_quit = 1
end
if (check_db_upgrade('sybsystemprocs') = 0)
begin
	print "'installcommit' cannot continue, because 'sybsystemprocs' database is not upgraded to correct version."
	select @do_quit = 1
end
if (@do_quit = 1)
	select syb_quit()
go

use master
go
if exists (select *
        from sysobjects
                where name = 'sp_configure')
begin
        execute sp_configure "allow updates", 1
end
reconfigure with override
go
use sybsystemprocs
go
exec sp_drop_object 'sp_start_xact', 'procedure'
go

exec sp_drop_object 'sp_commit_xact', 'procedure'
go

if ((select db_name()) = "master") 
	dump tran master with truncate_only
else
    if ((select db_name()) = "sybsystemprocs") 
	dump tran sybsystemprocs with truncate_only
    else
        if ((select db_name()) = "dbccdb") 
	    dump tran dbccdb with truncate_only
        else
            if ((select db_name()) = "dbccalt") 
	        dump tran dbccalt with truncate_only
go

exec sp_drop_object 'sp_abort_xact', 'procedure'
go

exec sp_drop_object 'sp_remove_xact', 'procedure'
go

exec sp_drop_object 'sp_stat_xact', 'procedure'
go

exec sp_drop_object 'sp_scan_xact', 'procedure'
go

exec sp_drop_object 'sp_probe_xact', 'procedure'
go

if ((select db_name()) = "master") 
	dump tran master with truncate_only
else
    if ((select db_name()) = "sybsystemprocs") 
	dump tran sybsystemprocs with truncate_only
    else
        if ((select db_name()) = "dbccdb") 
	    dump tran dbccdb with truncate_only
        else
            if ((select db_name()) = "dbccalt") 
	        dump tran dbccalt with truncate_only
go

use master
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_start_xact')
begin
	drop procedure sp_start_xact
end
go
print "Installing sp_start_xact"
go

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_commit_xact')
begin
	drop procedure sp_commit_xact
end
go
print "Installing sp_commit_xact"
go

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_abort_xact')
begin
	drop procedure sp_abort_xact
end
go
print "Installing sp_abort_xact"
go

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_remove_xact')
begin
	drop procedure sp_remove_xact
end
go
print "Installing sp_remove_xact"
go

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_stat_xact')
begin
	drop procedure sp_stat_xact
end
go
print "Installing sp_stat_xact"
go

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_scan_xact')
begin
	drop procedure sp_scan_xact
end
go
print "Installing sp_scan_xact"
go

if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_probe_xact')
begin
	drop procedure sp_probe_xact
end
go
print "Installing sp_probe_xact"
go

/*
** Messages for "commit service"        17380
**
** Note: these were Raiserror messages 30000, 30001, 30002
**
** 17380, "Commit service xact id doesn't exist."
*/
 
use sybsystemdb
go

if not exists (select *
		from sysobjects
			where name = "spt_committab"
				and sysstat & 7 = 3)
begin
execute ("
    CREATE TABLE	spt_committab
    (
	commid	int, 	/* id used to refer to transaction */
	suid	int, 	/* Server Login id of user that starts xa tran
		  	** branch.  This is only used by the XA interface. 
		       	*/
	start	datetime, /* time transaction started */
	lastchange datetime, /* last time this row was updated */
	totnum	smallint, /* number of servers 
			  ** initially involved in xact 
			  */
	outnum	smallint, /* number of servers 
			  ** who still have not completed 
			  */
	heur	char(1), /* 'y'- was heuristically completed */ 
	status	char(1), /* 'a'- abort, 
			    'c'- commit, 
			    'b'- begin, 
			    'p' - prepare */
	xid	 binary(140),	/* tran branch name */
	applname varchar(30), /* application name */
	xactname varchar(30), /* transaction name */
	password varchar(30) NULL /* password protection for updating */
    ) lock allpages
")
end
go

if not exists (select * from sysindexes where name = "commitclust")
begin
	create unique clustered index commitclust on spt_committab(commid) with ignore_dup_key
end
go

use sybsystemprocs
go

create procedure sp_start_xact 
	@applname varchar(30),
	@xactname varchar(30),
	@count	smallint,
	@password varchar(30) = NULL
AS

declare 	@xid	  binary(140)
declare 	@randid	  int

if @@trancount = 0
begin
	set transaction isolation level 1
	set chained off
end

set nocount on

BEGIN
	BEGIN TRANSACTION
		select @xid = convert(binary(140), 0x0)
		
		/*	
		** Get a random commid to reduce the page lock contention
		** during insert.  Note that there is a possibility of 
		** inserting a duplicate row.  If we do, then repeat the
		** insert with another commid until we are successful.
		** The probability is extremely low since we can have 
		** atleast 2 billion combinations (2147483647 to be precise)
		** for the commid.
		*/
		while ( 1 = 1 )
		begin
			/* Select a random commid between 1 and 2147483647 */
			select @randid = rand() * 2147483647
			INSERT sybsystemdb.dbo.spt_committab VALUES 
			(
                       	 	@randid,
				-1, /* this tells us that this is a non-XA 2pc */
                        	getdate(),
                        	getdate(),
				@count, 
				@count, 
				'n',
				'b',
				@xid,
				@applname, 
				@xactname,
				@password
			) 

			/* 
			** If we attempted to insert a duplicate row into the 
			** table we would have got a 2601 error.  But we have
			** some special checks in dupcheck() routine which will
			** avoid printing the error message if the object is 
			** spt_committab.  So, the user will not see an error
			** message in this situation.  If we get a 2601 error,
			** should try a different random commid.
			*/
			if ( @@error = 2601 )
				continue
			else
				/* Insert was successful. */
				break
		end

		if @@trancount = 0
		begin
			return 1
		end
			
                SELECT @randid
	COMMIT TRANSACTION
END
go

exec sp_procxmode 'sp_start_xact', 'anymode'
go
grant execute on sp_start_xact to public
go
dump tran sybsystemprocs with truncate_only
go

create procedure sp_commit_xact
	@commid	int,
	@password varchar(30) = NULL
AS


if @@trancount = 0
begin
	set transaction isolation level 1
	set chained off
end


if exists (select *
	from sybsystemdb.dbo.spt_committab
	where commid = @commid
	and password = @password)
begin
	/*
	**  If status is aborted, return a 1 to indicate a failure.
	*/
	if exists (select *
			from sybsystemdb.dbo.spt_committab
				where commid = @commid
					and status = 'a')
	begin
		select 1
		return (1)
	end

	/*  Mark the xact as committed. */
	UPDATE sybsystemdb.dbo.spt_committab 
		SET status = 'c', lastchange = getdate()
		WHERE commid = @commid
	select 0
	return (0)
end
else
begin
	/* Error so return 1 */
	/*
	** 17380, "Commit service xact id doesn't exist."
	*/
	raiserror 17380
	select 1
	return (1)
end
go

exec sp_procxmode 'sp_commit_xact', 'anymode'
go
grant execute on sp_commit_xact to public
go
dump tran sybsystemprocs with truncate_only
go

CREATE PROCEDURE sp_abort_xact
	@commid	int,
	@password varchar(30) = NULL
AS


if exists (select *
	from sybsystemdb.dbo.spt_committab
	where commid = @commid
	and password = @password)
begin
	UPDATE sybsystemdb.dbo.spt_committab 
		SET status = 'a' , lastchange = getdate()
		WHERE commid = @commid
END
else
begin
	/*
	** 17380, "Commit service xact id doesn't exist."
	*/
	raiserror 17380
end
go

exec sp_procxmode 'sp_abort_xact', 'anymode'
go
grant execute on sp_abort_xact to public
go
dump tran sybsystemprocs with truncate_only
go

CREATE PROCEDURE sp_remove_xact
	@commid	int,
	@count	smallint,
	@password varchar(30) = NULL
AS


if exists (select *
	from sybsystemdb.dbo.spt_committab
	where commid = @commid
	and password = @password)
begin
	/*
	** Decrement outnum by @count.
	*/
	UPDATE sybsystemdb.dbo.spt_committab 
		SET outnum = outnum - @count, lastchange = GETDATE()
		WHERE commid = @commid

	/*
	**  Delete the row if outnum < 1
	*/
	DELETE sybsystemdb.dbo.spt_committab
		WHERE commid = @commid
			and outnum < 1

end
else
begin
	/*
	** 17380, "Commit service xact id doesn't exist."
	*/
	raiserror 17380
end
go

exec sp_procxmode 'sp_remove_xact', 'anymode'
go
grant execute on sp_remove_xact to public
go
dump tran sybsystemprocs with truncate_only
go

create procedure sp_stat_xact
	@commid	int
AS
BEGIN
	if @@trancount = 0
	begin
		set transaction isolation level 1
		set chained off
	end

	IF EXISTS 
	(
		SELECT status 
			FROM sybsystemdb.dbo.spt_committab
			WHERE commid = @commid
	)
		SELECT status 
			FROM sybsystemdb.dbo.spt_committab
			WHERE commid = @commid
	ELSE
		SELECT status = 'u'
END
go

exec sp_procxmode 'sp_stat_xact', 'anymode'
go
grant execute on sp_stat_xact to public
go
dump tran sybsystemprocs with truncate_only
go

create procedure sp_scan_xact 
	@commid	int = NULL
as
BEGIN
	if @@trancount = 0
	begin
		set transaction isolation level 1
		set chained off
	end

	IF	@commid = -1 or @commid is NULL

		SELECT commid,start,lastchange,totnum,outnum,
		       status,applname,xactname
		FROM sybsystemdb.dbo.spt_committab
	ELSE
		SELECT commid,start,lastchange,totnum,outnum,
		       status,applname,xactname
		FROM sybsystemdb.dbo.spt_committab
		WHERE @commid = commid
END
go

exec sp_procxmode 'sp_scan_xact', 'anymode'
go
grant execute on sp_scan_xact to public
go
dump tran sybsystemprocs with truncate_only
go

use master
go

if exists (select *
		from sysobjects
			where name = "sp_probe_xact")
begin
        drop procedure sp_probe_xact
end
go

create procedure sp_probe_xact
	@commid	int,
	@recovery int = NULL
AS
BEGIN
	declare @stat	char(1)
	declare	@suid int

	if @@trancount = 0
	begin
		set transaction isolation level 1
		set chained off
	end

	begin tran
		IF EXISTS ( SELECT status 
				FROM sybsystemdb.dbo.spt_committab
					WHERE commid = @commid)
		begin
			SELECT @stat = status, @suid = suid
				FROM sybsystemdb.dbo.spt_committab
					WHERE commid = @commid

			/* Only abort non-xa xacts.  The TP Monitor reserves
			** the right to make this decision when xa is in use.
			** The suid is only set by the xa api.  For clasic
			** Sybase 2pc, suid is set to -1.
			*/
			if (@suid = -1) 
			begin
				if ((@stat != 'a') and (@stat != 'c'))
				begin
					update sybsystemdb.dbo.spt_committab
						set status = 'a'
							where commid = @commid
					select status = 'a'
				end
				else
					select status = @stat
			end
			else /* this is an xa tran branch */
			begin

				if (@stat = 'p')
				/* this is an xa tran branch in the prepared 
				** state 
				*/
				begin
					/* end this commit service tran */
					rollback tran

					/* wait for the TP Monitor to update 
					** commit status of this tran branch.
					*/
					while (@stat = 'p')
					begin
						/* check again ever 15 secs */
						waitfor delay "00:00:15"
						SELECT @stat = status
							FROM sybsystemdb.dbo.spt_committab
							WHERE commid = @commid
					end

					/* now return the status */
					select status = @stat
				end
				else
				begin
					if (@stat != 'c')
						select status = 'a'
					else
						select status = 'c'
				end
			end
		end
		else select status = 'a'
	if (@@trancount > 0)
		commit tran
END
go

exec sp_procxmode 'sp_probe_xact', 'anymode'
go
grant execute on sp_probe_xact to public
go
dump tran master with truncate_only
go

/*
** Copy the contents of master.dbo.spt_committab to 
** sybsystemdb.dbo.spt_committab if it exists.  
**
** We have to address 3 different scenarios here.
**
**	1. master.dbo.spt_committab exists and it has a older
**	   format. ie., it does not have suid, heur and xid fields.
**
**		Example: Prior to system 10.0 
**
**		In this case, we use alter table to bring the table
**		schema in consistent with the current format.  Then
**		the rows are copied from master.dbo.spt_committab to 
**		sybsystemdb.dbo.spt_committab.
**
**	2. master.dbo.spt_committab exists and is identical to
**	   the current format in this script.
**	
**		Example: System 10.0 to 11.0.2.
**
**		In this case, the tables in master and sybsystemdb
**		has identical schema and hence we need to just copy
**		the contents of master.dbo.spt_committab to 
**		sybsystemdb.dbo.spt_committab.
**
**	3. master.dbo.spt_committab does not exist. 
**		
**		Example: System 11.0.2.
**
**		 Note that we have listed 11.0.2 in scenario 2
**		 also.  A handful of XA customers received this
**		 script to enhance their XA performance.  These
**		 sites may not have a spt_committab in master
**		 at all.
**
**		 Another example is, some one rerun installcommit.
**
** We are creating a dummy spt_committab in master if one does not exist.
** Yes, it is strange but we need it here to appease the compiler/normalizer
** so that it wont complain  when the code which is responsible for an INSERT
** gets compiled (when master.dbo.spt_committab does not exist).
*/

use master
go

if not exists (select *
                from sysobjects
                        where name = "spt_committab"
                                and sysstat & 7 = 3)
begin
execute ("
    CREATE TABLE    spt_committab
    (
        commid  int, /* id used to refer to transaction */
        suid    int, /* Server Login id of user that starts xa tran
                          ** branch.  This is only used by the XA interface.
                          */
        start   datetime, /* time transaction started */
        lastchange datetime, /* last time this row was updated */
        totnum  smallint, /* number of servers
                          ** initially involved in xact
                          */
        outnum  smallint, /* number of servers
                          ** who still have not completed
                          */
        heur    char(1), /* 'y'- was heuristically completed */
        status  char(1), /* 'a'- abort,
                            'c'- commit,
                            'b'- begin,
                            'p' - prepare */
        xid      binary(140),   /* tran branch name */
        applname varchar(30), /* application name */
        xactname varchar(30), /* transaction name */
        password varchar(30) NULL /* password protection for updating */
    ) lock allpages
")
end
go

if not exists (select * from syscolumns where	id = object_id("spt_committab")
					and	name = "xid")
begin
	declare @cmd varchar(100)
	select @cmd = "alter table spt_committab add suid int null, heur char(1) null, xid binary(140) null"
	exec (@cmd)
end
go

if (select count(*) from spt_committab) > 0
begin
        begin tran
		insert into sybsystemdb.dbo.spt_committab
        	select commid, isnull(suid, @@maxsuid), start, lastchange, 
			totnum, outnum, isnull(heur, 'n'), status, 
			isnull(xid,' '), applname, xactname, password 
		from master.dbo.spt_committab 
	commit tran
end
go

drop table master.dbo.spt_committab
go

use master
go
dump transaction master with truncate_only
go
sp_configure "allow updates", 0
go
reconfigure with override
go
print 'Installcommit installation is complete.'
go

declare @retval int
exec @retval = sp_version 'installcommit', NULL, '15.5/EBF 17340 SMP/P/x86_64/Enterprise Linux/ase155/2391/64-bit/OPT/Mon Nov  9 14:15:35 2009', 'end'
if (@retval != 0) select syb_quit()
go

