Skip to Content
0

'create database' generated by 'load database' has an unnecessary line when database is shrinked

Jun 06, 2017 at 07:49 AM

98

avatar image

ASE 16.0 SP02

'load database ... with listonly=create_sql' generates DDL ('create database' statement) from a dump file.

When generating 'create database' statement from a shrinked database dump, it carries unnecessary line including 'unknown device'.

*** How to reproduce the issue ***

1. Create a database having data and log devices

1> create database db1 on datadev=200 log on logdev=100
2> go

2. Set "single user" option true to enable database shrinking

1> sp_dboption db1, "single", true
2> go

3. Make database size smaller by shrinking data device

1> alter database db1 off datadev=100
2> go

4. Dump database

1> dump database db1 to "db1.dmp"
2> go

5. Generate 'create database' statement by 'load database ... with listonly=create_sql'

1> load database db1 from "db1.dmp" with listonly=create_sql
2> go
DISK INIT
name = 'datadev'
, physname = '/export/home/ase16/data/datadev.dat'
, size = '200M'
, directio = true
go
DISK INIT
name = 'logdev'
, physname = '/export/home/ase16/data/logdev.dat'
, size = '100M'
, directio = true
go
CREATE DATABASE db1
ON datadev = '100M'
, unknown_device = '100M' ----> This line should be removed.
LOG ON logdev = '100M'
go

I believe the line including ' unknown_device =' is unnecessary and should be removed.
Any plan to fix this behavior in future releases?

Regards,
Kazuo Otani

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

7 Answers

Bret Halford
Jun 06, 2017 at 05:15 PM
0

Hi Kazuo,

The line actually is necessary, as without it the logical page numbers generated for the logdev fragment
would be contiguous with the first fragment, which wouldn't map up with the page numbers in the dump file.

What would be useful would be some way to specify creation of a gap of a particular size, such as on a null device,
and for the "create_sql" to generate output using that option.

You can bring feature requests to the attention of ASE product management by submitting them at
http://ideas.sap.com/sapase.

Cheers,
-bret

Share
10 |10000 characters needed characters left characters exceeded
Kazuo Otani Jun 07, 2017 at 05:42 AM
0

Hi Bret,

"ddlgen" gives me a "create database" statement that I want.
Let's compare two "create database"s generated by "load database" and "ddlgen"

- load database ... with listonly=create_sql

CREATE DATABASE db1
ON datadev = '100M'
, unknown_device = '100M'
LOG ON logdev = '100M'
go

- ddlgen

CREATE DATABASE db1
ON datadev = '100M' -- 51200 pages
LOG ON logdev = '100M' -- 51200 pages
WITH DURABILITY = FULL
go

I wonder "create database" by "load database ... create_sql" may mislead that database size is 300MB, while actual one is 200MB.
I would like "load database ... create_sql" to generate the same "create database" statement as "ddlgen" does.

Thank you for introducing me feature-request site.
I didn't know that.

Regards,
Kazuo Otani

Share
10 |10000 characters needed characters left characters exceeded
Mark A Parsons Jun 07, 2017 at 11:41 AM
0

In the dataserver where you ran 'alter database db1 off datadev=100', what do the contents of sysusages look like?

select * from master..sysusages where dbid = db_id('db1') order by lstart

If, as Bret's mentioned, there's a hole in the database page numbering then this will also show up in the sysusages data (and it would also point at a bug with ddlgen, ie, ddlgen should also generate the hole).

If there is no hole (in database page numbering) in the sysusages data then ddlgen would appear to be correct and the dump header data incorrect.

Share
10 |10000 characters needed characters left characters exceeded
Kazuo Otani Jun 08, 2017 at 06:25 AM
0
1> create database db1 on datadev=200 log on logdev=100
2> go
1> alter database db1 off datadev=100
2> go
1> select dbid,segmap,lstart,size,vstart from sysusages where dbid=db_id("db1")
2> go
dbid segmap lstart size vstart
------ ----------- ---------- --------- -----------
8 3 0 51200 0
8 0 51200 51200 51200
8 4 102400 51200 0
Share
10 |10000 characters needed characters left characters exceeded
Mark A Parsons Jun 08, 2017 at 12:10 PM
0

Your initial create database command allocated 300MB of pages (153,600 pages); these pages where numbered 0 to 153,599. At that time Sybase had the ability to utilize all 153,600 pages.

Your alter database command told Sybase to remove 100MB of pages from use; this shows up as the 2nd sysusages entry with segmap = 0; this 100MB hole exists because Sybase does *not* renumber the remaining pages to fill in the gap; net result is that Sybase now has 200MB of pages to utilize, with said pages numbered 0 to 51,199 and 102,400 to 153,599.

When your database is dumped the page numbers are maintained, ie, usable data pages are numbered 0 to 51,199 and 102,400 to 153,599.

When you load your database the page numbers are maintained, again, usable data pages are numbered 0 to 51,199 and 102,400 to 153,599.

The load database/listonly command correctly generates the create database command with a hole it in so as to maintain the proper page numbering.

As Bret's mentioned, what would be nice is a feature that allows the DBA to create a database with a hole in it (as opposed to having to allocate space on a device you're not going to use).

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

[deleted] rambling about ddlgen and create database ... Kazuo's example, and a few follow-up tests of my own, ASE is able to set aside a hole in sysusages.

Share
10 |10000 characters needed characters left characters exceeded
Kazuo Otani Jun 09, 2017 at 06:04 AM
0

Mark,

> I suggest you run the ddlgen generated create database command and then try to load your db dump file into said database ... it should generate an error telling you the the database is too small.

I tried it.
Plaese see command sequence.

1> create database db1 on datadev=200 log on logdev=100
2> go
1> alter database db1 off datadev=100
2> go
1> dump database db1 to "db1.dmp"
2> go
1> drop database db1
2> go
1> create database db1 on datadev=100 log on logdev=100 for load
2> go
1> load database db1 from "db1.dmp"
2> go
1> online database db1
2> go

load database works fine.
It recognizes actual database size even if dump file has a hole in page numbering.
So, it looks ddlgen generated create database has no problem.

After loading, sysusages shows the same result I posted above.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

*arg* Thanks for the wake-up call. I ran some tests on 15.7 and 16.0 and you're correct, ASE auto-magically creates the necessary hole in sysusages when loading the database. I've removed my nonsensical ramblings from my other post.

0
Kazuo Otani Jun 12, 2017 at 06:19 AM
0

Does create database command generated by load database ... with listonly=create_sql stay unchanged or go to CR when sysusages has a hole in page numbering?

Share
10 |10000 characters needed characters left characters exceeded