Question Re: load database xx with listonly=create_sql giving wrong ddl for database in Technology Q&A

load database xx with listonly=create_sql giving wrong ddl for database

Former Member — Thu, 24 Mar 2016 15:27:07 GMT

Database size increased by 7.5 MB . alter with non whole number ( like 7.5 MB) in my case .

When we use new feature to generate database schema using lisonly=create_sql . It takes floar value and suggest to alter database size by 7 MB.

but if we generate ddl from sybase central or ddlegen then it suggest to alter database by 8 MB .--> Which looks correct .

As target DB size should be same size or bigger size to load database .

My Case :

sp_helpdb xx --> See care fully xxx_data04 --7.5 MB

=========

xxx_data01 500.0 MB data only Jun 19 2009 12:25PM 30

xxx_log01 100.0 MB log only Jun 19 2009 12:25PM not applicable

xxx_data01 3000.0 MB data only Jun 19 2009 12:25PM 56

xxx_log01 1000.0 MB log only Jun 19 2009 12:25PM not applicable

xxx_data01 2000.0 MB data only Jun 19 2009 12:25PM 14

xxx_log01 2000.0 MB log only Jun 19 2009 12:25PM not applicable

xxx_data02 4000.0 MB data only Jun 19 2009 12:25PM 168

xxx_data01 2000.0 MB data only Jun 19 2009 12:25PM 28

xxx_data03 4000.0 MB data only Mar 1 2010 4:22PM 0

xxx_data04 7.5 MB data only Jul 7 2011 10:28AM 0

xxx_data05 4000.0 MB data only Jul 7 2011 10:31AM 0

xxx_data06 4000.0 MB data only Jun 29 2012 1:26PM 0

xxx_data01 500.0 MB data only Mar 12 2014 10:09AM 0

xxx_data07 4000.0 MB data only May 9 2014 11:57AM 0

xxx_data08 4000.0 MB data only Jan 26 2015 10:31AM 0

xxx_data09 4000.0 MB data only Jun 29 2015 5:12AM 3132118

load database xxx with listonly=create_sql -->> See care fully , xxx_data04 = '7M' --> it should be 7.5 MB

=============================

CREATE DATABASE xxx

ON xxx_data01 = '500M'

LOG ON xxx_log01 = '100M'

ALTER DATABASE xxx

ON xxx_data01 = '3000M'

LOG ON xxx_log01 = '1000M'

ALTER DATABASE xxx

ON xxx_data01 = '2000M'

LOG ON xxx_log01 = '2000M'

ALTER DATABASE xxx

ON xxx_data02 = '4000M'

, xxx_data01 = '2000M'

, xxx_data03 = '4000M'

, xxx_data04 = '7M'

, xxx_data05 = '4000M'

, xxx_data06 = '4000M'

, xxx_data01 = '500M'

, xxx_data07 = '4000M'

, xxx_data08 = '4000M'

, xxx_data09 = '4000M'

==== DDLGEN /Sybase Central

Every thing same except :

ALTER DATABASE bat

ON bat_data04 = '8M' -- 3840 pages

Appreciate your suggestion ...

Re: load database xx with listonly=create_sql giving wrong ddl for database

Former Member — Thu, 24 Mar 2016 20:02:47 GMT

looks like enough info to open a case with SAP/Sybase.

Re: load database xx with listonly=create_sql giving wrong ddl for database

dan_thrall — Sat, 02 Apr 2016 00:05:34 GMT

Hi Pandey,

Adding notes to the SCN after the Incident was created for benefit of those curious about this report.

Whenever creating or altering for space, we allocate as many Allocation Units (AU) that are available up until the requested amount.

What this means is let's say we have 10 megs free on a device and you try to alter for 20. The alter will be successful, giving you 10 megs.

Now enter in this problem. For whatever reason, the size of this device has a non-rounded number of megabytes free. There are a couple ways this can happen and most commonly seen on raw partitions when the size of the partition isn't quite a whole meg in size.

Some math -

This ASE is 2k page size. An AU is 256 pages in size.

This means an AU 256*2048 = 524288 bytes or .5 megabyte.

Now going back to the alter logic. We will alter as long as we have free AU's. In the case of 2k page, this also means we can get into (1.0*sysusages.size/512) to be a non-rounded number as seen in the math above.

Why does create_sql not indicate this?

The short answer is create/alter database accepts whole numbers for size specification and not pages.

The technical answer is only integer values are accepted.

Added reason here, is because of the whole number integers for create/alter, the create_sql is not checking for half meg fragments.

I've logged CR 797291 to have create_sql be half meg aware.

How do you create so you can load this database?

Using the example above:

You can disk init xxx_data04 to be 7.5M, then when create/alter specify "on xxx_data04='8M'" which in turn will give you the 7.5 megs. To be safe, have this particular alter all by itself, just to avoid confusion. This works because device sizes can be created for a specified page count.

You can also do a bit of sysusages manipulation during the process which I don't recommend, since updating system tables can be troublesome if not handled carefully. Essentially it boils down to altering for 8M, subtracting 256 pages (.5M) from size to make 7.5M , run dbcc dbrepair(dbname, remap), then proceed with altering. Use at your own risk, use a test server first, have master backups, use a transaction, etc.

HTH

Dan

Re: load database xx with listonly=create_sql giving wrong ddl for database

Former Member — Sat, 02 Apr 2016 20:46:51 GMT

Dan,

Excellent lesson for us all. I particularly like the simplicity and effectiveness of

"You can disk init xxx_data04 to be 7.5M, then when create/alter specify "on xxx_data04='8M'" which in turn will give you the 7.5 megs."

LOL!

Regards,

Jean-Pierre

Re: load database xx with listonly=create_sql giving wrong ddl for database

Former Member — Sat, 02 Apr 2016 20:51:49 GMT

P.S. As far as the manipulation you are talking about... Are you talking about a direct update to the master..sysusages 8MB fragment for the database? Thank you. Regards, Jean-Pierre

Re: load database xx with listonly=create_sql giving wrong ddl for database

former_member188958 — Sun, 03 Apr 2016 16:34:09 GMT

Yes, the final method Dan is talking about is doing direct updates to master..sysusages.

-bret

Re: load database xx with listonly=create_sql giving wrong ddl for database

Former Member — Mon, 04 Apr 2016 13:54:26 GMT

We have same proble when we generate ddl from ddlgen and sybase central ...

create_sql -- takes Floor values , ddlgen/sybase central take ceiling values .

Regards

Ajay Pandey

Re: load database xx with listonly=create_sql giving wrong ddl for database

Former Member — Mon, 04 Apr 2016 13:56:57 GMT

my problem is similar but i do not have the luxury of creating a device just for this database. how can i create the database on an existing device ?

Re: load database xx with listonly=create_sql giving wrong ddl for database

dan_thrall — Mon, 04 Apr 2016 23:31:36 GMT

Hi David,

The safest route is to get to the .5 meg fragment naturally. What I mean is you would alter on an existing device that just so happens to have a .5 meg AU at the end. You can fill the device with a dummy database first leaving the desired amount free, then alter as needed. Once this is done then finally drop the dummy filler database.

Here's a way to quickly check if you have a .5 meg device on 2k page ASE

1> select name,(1.0*high+1-low)/512 from sysdevices where (1.0*high+1-low)/512 %1 !=0 and status !=16

2> go

name

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

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

halfmeg

7.500000000000

(1 row affected)

The manual update method can be done. Again, throwing considerable caution here as modifying sysusages can get you into trouble very quickly if you aren't careful.

Finally the other safe route is a database can load as long as it is the same size or larger than the dump. The fragments should remap.

Using the example from the first posting

1> select segmap, lstart, size from sysusages where dbid=db_id("test")

2> go

segmap lstart size

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

3 0 5120

4 5120 5120

3 10240 3840 <<This is the 7.5M fragment

4 14080 5120

(4 rows affected)

Let's create and load a copy.

1> create database testload on reprodata=10 log on reprolog=10

2> go

1> alter database testload on reprodata=8 --this will not be 7.5, but a full 8M

2> go

1> alter database testload log on reprolog=10

2> go

--sysusages BEFORE load

1> select segmap, lstart, size from sysusages where dbid=db_id("testload")

2> go

segmap lstart size

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

3 0 5120

4 5120 5120

3 10240 4096

4 14336 5120

Now let's load the dump of the source to it.

1> load database testload from "/tmp/test.dmp"

2> go

[snip]

Backup Server: 3.42.1.1: LOAD is complete (database testload).

All dumped pages have been loaded. ASE is now clearing pages above page 19200,

which were not present in the database just loaded.

ASE has finished clearing database pages.

...

1> online database testload

2> go

1> select segmap, lstart, size from sysusages where dbid=db_id("testload")

2> go

segmap lstart size

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

3 0 5120

4 5120 5120

3 10240 3840

4 14080 5120

3 19200 256

(5 rows affected)

Now you can see the extra unused .5 meg is tacked on to the end and the 7.5M fragment is regenerated just as it existed in the dump.

You could then do some tricks to swallow this space up into a full fragment.

Options include

"alter database off" to remove it entirely

make your next alter add a half meg using the same logic discussed in this thread.

Since we are only looking at a single AU, the shortest route to a final solution might be to go straight to alter database off to shrink 256 pages from the device fragment. Future dump/load can reconcile the database hole. Alter database off accepts page ranges.

Cheers

Dan

Re: load database xx with listonly=create_sql giving wrong ddl for database

Former Member — Tue, 05 Apr 2016 00:05:38 GMT

Thank you for confirming Bret. Your answer though, makes me laugh... So mysterious. To me it looks like a pretty straight forword update to master..sysusages... If I had a server to practice on, I would send you guys the code.

Either way, I really enjoyed, the thread. Good simple work around in the end.

Cheers.

Jean-Pierre